Delete columns in spreadsheet based on list of column names specified in listed range
Hi
I have the code below that runs fine. It deletes all columns in a spreadsheet (sheet("Data")) that are not contained in the case statement. However instead of specifying the columns not to delete within the vba case statement code every time, I would like to read the columns from a range listed in a spreadsheet instead, e.g in another sheet.
I've tried to do it using a range reference but it does not work.
Setup as follows.
a) Data to delete is in the following sheet:
Sheets("Data")
To loop from column 29 to last as per current code. The column headers are in row 1:
b) List column names not to delete are listed here (instead of the code):
headerstodelete = Sheets("ColDeletionList").Range ("A2:A50").value
Thanks
Yoda
Code:
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