Excel VBA - Delete columns if column name <> "string name"

Question

I have data that I frequently export from an source workbook (format is consistent, i.e., all column names are located in the 1st row). I only need the data from about 8 or so columns spaced sporadically throughout the worksheet ("Sheet1"). Below is the code I have so far to do this.

I am having two problems,

1. the macro is deleting all of my data, and

2. My If statement is very inefficient. I would like to create a variable that includes all the strings containing the column names I wish to not delete. I'm thinking creating an array, but I am not sure if my loop would act correctly

   i.e., colNames = array("Col 1 that I want to keep", "Col 2", etc)
   

The Code:

Sub test2()

Dim currentSht As Worksheet
Dim i As Integer
Dim lastRow As Long, lastCol As Long
Dim startCell As Range

Set currentSht = ActiveWorkbook.Sheets("Sheet1")
Set startCell = currentSht.Range("A1")

lastRow = startCell.SpecialCells(xlCellTypeLastCell).Row
lastCol = startCell.SpecialCells(xlCellTypeLastCell).Column

With currentSht
    For i = lastCol To 1 Step -1
        If .Cells(1, i).Text <> "First Name" Or .Cells(1, i).Text <> "Last Name" Or .Cells(1, i).Text <> "OB Hours(hr)" Or .Cells(1, i).Text <> "OB Talk Time (TT)" Or .Cells(1, i).Text <> "OB ACW Time" Or .Cells(1, i).Text <> "Handled-OB" Or .Cells(1, i).Text <> "Handled-OB/hr" Or .Cells(1, i).Text <> "TT Avg" Or .Cells(1, i).Text <> "Max Talk Time" Then
        Columns(i).EntireColumn.Delete
    End If
Next i
End With

End Sub 

Thanks again everyone.

Answer 1

To your first question:

Change the Or to And in the if statement

To your second:

Sub test2()

Dim currentSht As Worksheet
Dim i As Long, j As Long
Dim lastRow As Long, lastCol As Long
Dim startCell As Range
Dim colnames
Dim here As Boolean

colnames = Array("First Name", "Last Name", "OB Hours(hr)", "OB Talk Time (TT)", "OB ACW Time", "Handled-OB", "Handled-OB/hr", "TT Avg", "Max Talk Time")

Set currentSht = ActiveWorkbook.Sheets("Sheet1")
Set startCell = currentSht.Range("A1")

lastRow = startCell.SpecialCells(xlCellTypeLastCell).Row
lastCol = startCell.SpecialCells(xlCellTypeLastCell).Column

With currentSht
    For i = lastCol To 1 Step -1
        here = False
        For j = LBound(colnames) To UBound(colnames)
            If .Cells(1, i).Value = colnames(j) Then
                here = True
                Exit For
            End If
        Next j
        If Not here Then
            Columns(i).EntireColumn.Delete
        End If        
    Next i
End With

End Sub