vba find column names and delete invert selection code example

Example: vba find column names and delete invert selection

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