none
Developing Macro to Automate File Prep RRS feed

  • Question

  • Good afternoon, all -

    First of all, if this is the wrong forum, would someone please move this question to where it should be? Thanx!

    What I'm doing is trying to automate a process to edit an Excel sheet that is created by importing the contents of a .csv file using Data|From Text/CSV. The basic source file has columns that I do not need and I want them deleted. So, I turned on Developer|Record Macro and set off deleting the unwanted columns.

    When I finished, I hit ^Home to sent the focus back to cell A1, stopped recording and clicked on View Code to admire my handiwork - and, to learn what was going on.

    Accordingly, there were several lines of code selecting the particular columns and then deleting them. Very straightforward. But, at the end was a line - presumably the ^Home - saying;

    Range("Test_CSV[[#Headers],[Facility Name]]").Select

    When I saw that the code had the column header name in it, I wondered if that sort of referential designation could be used to define what columns needed to be deleted. I was concerned that the website from which I get the original .csv file may not always provide the data in the exact same column order. Or, that they might add or subtract a column with no notice.

    So, I immediately jumped on here to ask the gurus if it's possible to select and then delete a column using the contents of the topmost cell - the column header - as a reference to identify the specific column. Is this possible?

    Thanx in advance for any assistance!

    Wednesday, May 1, 2019 7:49 PM

Answers

  • Adam,
    Re:  " if it's possible to select and then delete a column using the contents of the topmost cell"

    Fill in the required column titles in the array below and give the code a try.
    Note: columns that don't have a title listed in the array will be deleted.
    Title must appear in the first cell of the column (row 1).
    '---
    Sub DeleteSomeColumns()
    Dim rng As Excel.Range
    Dim rngItem As Long
    Dim vItem As Variant
    Dim bGood As Boolean
    Dim vHeaders As Variant

    With Worksheets("Sheet1")
      Set rng = .Range("A1", .Cells(1, .Columns.Count).End(xlToLeft)).Cells
    End With

    'List desired header titles in array below (adjust length as necessary)
    vHeaders = Array("larry", "moe", "curly", "always", "be", "good", "buy", "low", "sell", "high")

    For rngItem = rng.Cells.Count To 1 Step -1
      For Each vItem In vHeaders
        If vItem = rng(rngItem).Value Then
         bGood = True
         Exit For
        End If
     Next
     If Not bGood Then rng(rngItem).EntireColumn.Delete
     bGood = False
     Next
    End Sub
    '---

    Excel programs available (including Custom_Functions add-in with 19 new functions)
    Download from MediaFire
    http://www.mediafire.com/folder/lto3hbhyq0hcf/Documents



    Wednesday, May 1, 2019 11:47 PM

All replies

  • Adam,
    Re:  " if it's possible to select and then delete a column using the contents of the topmost cell"

    Fill in the required column titles in the array below and give the code a try.
    Note: columns that don't have a title listed in the array will be deleted.
    Title must appear in the first cell of the column (row 1).
    '---
    Sub DeleteSomeColumns()
    Dim rng As Excel.Range
    Dim rngItem As Long
    Dim vItem As Variant
    Dim bGood As Boolean
    Dim vHeaders As Variant

    With Worksheets("Sheet1")
      Set rng = .Range("A1", .Cells(1, .Columns.Count).End(xlToLeft)).Cells
    End With

    'List desired header titles in array below (adjust length as necessary)
    vHeaders = Array("larry", "moe", "curly", "always", "be", "good", "buy", "low", "sell", "high")

    For rngItem = rng.Cells.Count To 1 Step -1
      For Each vItem In vHeaders
        If vItem = rng(rngItem).Value Then
         bGood = True
         Exit For
        End If
     Next
     If Not bGood Then rng(rngItem).EntireColumn.Delete
     bGood = False
     Next
    End Sub
    '---

    Excel programs available (including Custom_Functions add-in with 19 new functions)
    Download from MediaFire
    http://www.mediafire.com/folder/lto3hbhyq0hcf/Documents



    Wednesday, May 1, 2019 11:47 PM
  • Adam,
    Re:  " if it's possible to select and then delete a column using the contents of the topmost cell"

    Fill in the required column titles in the array below and give the code a try.
    Note: columns that don't have a title listed in the array will be deleted.
    Title must appear in the first cell of the column (row 1).

    ...

    Well, cool!

    As I was pondering this problem last night, I considered that a table listing the required columns would be best. That way, if columns were added, I'd not need to go in and modify the code, especially if the change happened when I wasn't the one running the update and the other party wasn't paying close enough attention because of inexperience.

    I will give that a try. Thanx!

    Thursday, May 2, 2019 12:09 PM
  • Adam,

    Note that the worksheet name will also require updating...
      With Worksheets("Sheet1")
    Thursday, May 2, 2019 12:58 PM
  • Adam,

    Note that the worksheet name will also require updating...
      With Worksheets("Sheet1")

    Indeed. I replaced that with;

    With Worksheets(ActiveSheet.Name)
    I think that will do it.
    Thursday, May 2, 2019 1:00 PM
  • Adam,
    Re:  " if it's possible to select and then delete a column using the contents of the topmost cell"

    Fill in the required column titles in the array below and give the code a try.
    Note: columns that don't have a title listed in the array will be deleted.
    Title must appear in the first cell of the column (row 1).

    ...



    That worked like a charm! Thanx, NLtL.
    Thursday, May 2, 2019 4:10 PM