CODEWITHSUNDEEP
excelvba
eyeScream
eyeScream

Reputation: 71

Selecting multiple column headers and then deleting those columns

PLEASE EXCUSE ANY MISTAKES FOR NOT FOLLOWING PROPER POSTING FORMAT. THIS IS MY FIRST POSTING

I have a headings in row 1. I want to select multiple column headings and then delete those columns. I also want the code to not break if a certain heading specified doesn't exist (i.e. if a heading that it is searching for doesn't exist, the loop continues).

I've got code that works to find a specified heading and delete that column. However, I don't want to do this code for each heading to delete (roughly 20 headings). I also don't know how to make it so that it doesn't break if a heading doesn't exist. This will be something that I do on a monthly basis and most likely all the headings will always be the same, but I can't guarantee that. I downloaded a .csv file and work with that.

The MsgBox in my code is from an example I found online. I don't actually want a message box if it doesn't find a heading. I just want it to skip over a heading it doesn't find and continue searching for the other ones.

Find a column header and delete that column
    Dim rngHeadings As Range
    Set rngHeadings = Range("A1", Range("A1").End(xlToRight)).Find("Category")
        If rngHeadings Is Nothing Then
            MsgBox "Can't find that"
            Exit Sub
        End If
        rngHeadings.EntireColumn.Select
        Selection.Delete

So "Category" is one of the headings. Some others are "AirDur", "RefNbr", etc. (I can fill in the rest).

Upvotes: 0

Views: 138

Answers (2)

FAB
FAB

Reputation: 2569

Here is my try at answering this, please see coments for details.

Sub delColumns()

Dim ws As Worksheet: Set ws = ThisWorkbook.ActiveSheet 'better use Thisworkbook.Sheets("SheetName") / or ActiveWorkbook / or specific workbook
Dim lCol As Long: lCol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column 'Get the last column
Dim R As Long, C As Long

Dim arrHeadingsToDelete() As String
arrHeadingsToDelete = Split("Category,AirDur,RefNbr", ",")              'Add more as needed

For C = lCol To 1 Step -1                                               'Loop from last column to first
    For R = LBound(arrHeadingsToDelete) To UBound(arrHeadingsToDelete)  'Loop through each of the headings in the above array
        If ws.Cells(1, C) = arrHeadingsToDelete(R) Then                 'If there is a match
            ws.Cells(1, C).EntireColumn.Delete                          'Delete the column...
            Exit For                                                    '...and move to check the next one
        End If
    Next R
Next C

End Sub

Upvotes: 1

Tim Williams
Tim Williams

Reputation: 166296

Dim rngHeadings As Range, f As Range
Set rngHeadings = ActiveSheet.Rows(1)

For Each h in Array("Category","AirDur")
    Set f = rngHeadings.Find(What:=h, lookat:=xlWhole)
    If Not f Is Nothing Then f.EntireColumn.delete
Next h

Upvotes: 2

Related Questions