Deleting columns that don't have certain string

Question

I'm working on a data clean up project in Excel and working to build a macro that cleans up and deletes out unnecessary columns in a report.

I'm having issues looping through the columns to delete columns that don't have certain header text (I have 3 separate strings that I am looking to find and not delete).

I found a previous question that helped me get started Here.

How would I go about expanding this code to take into account multiple strings?

The code I attempted is below.

Dim currentColumn As Integer
Dim columnHeading As String

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 "Account Name", "Account ID", "Contract ID", "Delivery Date"
            'Do nothing
        Case Else
            'Delete if the cell doesn't contain "string-"
            If InStr(1, _
               ActiveSheet.UsedRange.Cells(1, currentColumn).Value, "string1-" Or "string2-" Or "string3-", vbBinaryCompare) = 0 Then

                ActiveSheet.Columns(currentColumn).Delete

            End If

    End Select
Next

Thanks!

dwirony dwirony · Accepted Answer · 2018-06-11T18:43:36

You can't embed an Or statement in InStr like that - you'll have to use multiple InStrs to test against your columns. Also, since you're deleting columns that contain the string, you need to check if InStr returns a value greater than zero, not equal to:

Sub Test()

Dim currentColumn As Integer
Dim columnHeading As String

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 "Account Name", "Account ID", "Contract ID", "Delivery Date"
            'Do nothing
        Case Else
            'Delete if the cell doesn't contain "string-"
            If InStr(columnHeading, "string1-") = 0 And _
               InStr(columnHeading, "string2-") = 0 And _
               InStr(columnHeading, "string3-") = 0 Then

                ActiveSheet.Columns(currentColumn).Delete

            End If

    End Select
Next

End Sub

Deleting columns that don't have certain string

1 Answers