Aussiebear
04-02-2024, 03:32 AM
I recently came across a post where the creator of the code used this:
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.
Clearly the logic here was if the value "Homer" didn't exist then delete the column, so why go to all the hassle of the first case listing all the exemptions. So ignoring the logic of this for a moment, is there a limit to how much you can stick inside a Case (definitions)?
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.
Clearly the logic here was if the value "Homer" didn't exist then delete the column, so why go to all the hassle of the first case listing all the exemptions. So ignoring the logic of this for a moment, is there a limit to how much you can stick inside a Case (definitions)?