Sub deleteIrrelevantColumns()
Dim currentColumn As Integer
Dim columnHeading As String
' ActiveSheet.Columns("G").Delete
' List = Sheets("to keep").Range("D1:D30")
currentColumn = 5
For currentColumn = ActiveSheet.UsedRange.Columns.Count To 29 Step -1 ' start from last col to 29 onwards only
columnHeading = ActiveSheet.UsedRange.Cells(1, currentColumn).Value
'CHECK WHETHER TO KEEP THE COLUMN
Select Case columnHeading
' Insert list reference here instead of specifying in code
' headerstodelete
Case "Acq_WK_1", "Acq_WK_20", "Acq_WK_34", "Area_Hemel_Hempstead", "Area_Reading", "Area_South_West_London", "ctype_guest", "email", "fo_Category_KNITWEAR", _
"fo_Category_OUTERWEAR", "fo_Category_WOVEN", "fo_Category_WOVEN_TROUSERS", "fo_device_mobile", "fo_discount_dummy", "fo_discountandfreedelivery_dummy", _
"fo_discountrate", "fo_freedelivery_dummy", "fo_Mth_Aug", "fo_part_returner_dummy", "fo_total_discountorderstable", "fo_totalvalue", "fo_visit_cpc", "fo_visit_display", _
"fo_visit_email", "Forecast", "mailedbook_andy", "multi_order_customer", "recency_dayssincelastorder", "visits", "gets_email_andyandnotsubscribed", "Acq_MTH_1", "Acq_MTH_10", _
"Acq_MTH_11", "Acq_MTH_3", "Acq_MTH_4", "Acq_MTH_5", "Acq_MTH_6", "Acq_MTH_9", "Acq_WK_10", "Acq_WK_16", "Acq_WK_19", "Acq_WK_40", "acquisition_year", _
"age_missing_dummy", "age_nullsreplavg", "Area_Aberdeen", "Area_Guildford", "Area_North_London", "Area_Redhill", "Area_South_West_London", "Area_West_London", _
"cold_book_redeem", "ctype_customer", "ctype_guest", "ctype_prospect", "gender_missing_dummy", "gendermale_dummy", "gets_email_andy", "households_avg_repzero", _
"location_london", "mailedbook_andy", "no_postcode_dummy", "population_avg_repzero", "propensityscore_andy", "unsubscribed_from_email", "visits"
'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
Where the first Case is stuffed full of everything but the kitchen sink, only to do nothing if true. Whereas the real test is hidden in the second Case "if it didn't contain the value "Homer" then delete the column.