vba - Deleting all columns except columns with certain headings

Question

I am trying to format exported data and need to delete several columns. I want to keep columns with certain headings. For convenience if I have 15 columns and want to keep columns with the following headings;

State City Name Client Product

My concern is that the data I am exporting can change and columns could be added in the future. I want to be able to filter out unwanted columns and just keep the aforementioned columns.

Edit: I am using Excel and I have never written a line of code before in my life. Ever.

Answer 1

Try this one.

Iterate over the columns in reverse order, check the headers in a Select Case, and delete as needed.

Sub deleteIrrelevantColumns()
    Dim currentColumn As Integer
    Dim columnHeading As String

    ActiveSheet.Columns("L").Delete

    For currentColumn = ActiveSheet.UsedRange.Columns.Count To 1 Step -1

        columnHeading = ActiveSheet.UsedRange.Cells(1, currentColumn).Value

        'CHECK WHETHER TO KEEP THE COLUMN
        Select Case columnHeading
            Case "State", "City", "Name", "Client", "Product"
                'Do nothing
            Case Else
                'Delete if the cell doesn't contain "Homer"
                If Instr(1, _
                   ActiveSheet.UsedRange.Cells(1, currentColumn).Value, _
                   "Homer",vbBinaryCompare) = 0 Then

                    ActiveSheet.Columns(currentColumn).Delete

                End If
        End Select
    Next

End Sub