Удалить столбцы с определенным заголовком

Sub deleteIrrelevantColumns()
    Dim keepColumn As Boolean
    Dim currentColumn As Integer
    Dim columnHeading As String
 
    currentColumn = 1
    While currentColumn <= ActiveSheet.UsedRange.Columns.Count
        columnHeading = ActiveSheet.UsedRange.Cells(1, currentColumn).Value
 
        'CHECK WHETHER TO KEEP THE COLUMN
        keepColumn = False
        If columnHeading = "State" Then keepColumn = True
        If columnHeading = "Customer name" Then keepColumn = True
        If columnHeading = "Gallons" Then keepColumn = True
        If columnHeading = "Supplier" Then keepColumn = True
        If columnHeading = "Carrier" Then keepColumn = True
 
 
        If keepColumn Then
        'IF YES THEN SKIP TO THE NEXT COLUMN,
            currentColumn = currentColumn + 1
        Else
        'IF NO DELETE THE COLUMN
            ActiveSheet.Columns(currentColumn).Delete
        End If
 
        'LASTLY AN ESCAPE IN CASE THE SHEET HAS NO COLUMNS LEFT
        If (ActiveSheet.UsedRange.Address = "$A$1") And (ActiveSheet.Range("$A$1").Text = "") Then Exit Sub
    Wend
 
End Sub

Private Sub Test()
    Dim iCell As Range, iColumn As Variant
    For Each iColumn In Array("State", "Customer name", "Gallons", "Supplier", "Carrier")
        Set iCell = ActiveSheet.UsedRange.Rows(1).Find(iColumn, , xlValues, xlWhole, , , False)
        If Not iCell Is Nothing Then iCell.EntireColumn.Delete
    Next
End Sub

Private Sub Test2()
    Application.ScreenUpdating = False
    Application.Calculation = xlManual
    
    Dim iHeader As Range, iCell As Range, iColumn As Variant
    Set iHeader = ActiveSheet.UsedRange.Rows(1)
    For Each iColumn In Array("State", "Customer name", "Gallons", "Supplier", "Carrier")
        Set iCell = iHeader.Find(iColumn, , xlValues, xlWhole, , , False)
        If Not iCell Is Nothing Then iCell.EntireColumn.Delete
    Next
    
    Application.Calculation = xlAutomatic
    Application.ScreenUpdating = True
End Sub

Sub NorthernPlains()
 Dim MyCol As String
 Dim i As Integer
For i = 1 To Range("C" & "65536").End(xlUp).Row Step 1
If Application.WorksheetFunction.CountIf(Range("A" & i & ":AZ" & i), "IL") > 0 Then
 Range("C" & i).EntireRow.Delete
End If
Next i
 
 End Sub

Private Sub Test3()
    Dim iRow&
    For iRow = Cells(Rows.Count, "H").End(xlUp).Row To 1 Step -1
        If Application.CountIf(Cells(iRow, "H"), "IL") > 0 Then Rows(iRow).Delete
    Next
End Sub

Private Sub Test4()
    Dim iSource As Range, iCell As Range
    Set iSource = [H:H]: Set iCell = iSource.Find("IL", , xlValues, xlWhole)
    Do Until iCell Is Nothing
       iCell.EntireRow.Delete
       Set iCell = iSource.FindNext
    Loop
End Sub

Set iSource = [H:H]: Set iCell = iSource.Find("IL", "IN", "IA" , xlValues, xlWhole)

Private Sub Test3v2()
    Dim iRow&, iArr As Variant
    iArr = Array("IL", "IN", "IA")
    
    With Application
         For iRow = Cells(Rows.Count, 8).End(xlUp).Row To 1 Step -1
             If .Sum(.CountIf(Cells(iRow, 8), iArr)) > 0 Then Rows(iRow).Delete
         Next
    End With
End Sub