Excel VBA CASE compare to list

user_19457948 0
Viewed 36

Not too familiar with VBA but was able to google/modify the below macro. Macro is to look at column header and delete columns that do not match certain text. Macro has worked as intended. I'm trying to understand if it's possible to have the list in a xls sheet (e.g Column A in "internal" worksheet) and update the list there instead of within the code? In the future, i'll need to add/modify headers that I need to keep and prefer to just simply update a column in xls worksheet than in the macro itself. I believe an array can be used but not sure how to properly code it.

Dim lcol As Long
Dim vtfc As Long

lcol = ActiveSheet.Cells.Find(What:="*", SearchOrder:=xlByColumns, _
        SearchDirection:=xlPrevious, LookIn:=xlValues).Column
    
  For vtfc = lcol To 1 Step -1
        Select Case Cells(1, vtfc)
        Case "Qty", "Product", "License ID", "Name", "Site Type", "Region", "Country", "State", "City", "Street", "Zip Code",  "Global Access", "Start Date", "Term"
        'Will do nothing if the value is matched
        Case Else
        Columns(vtfc).Delete
        End Select
  Next
vba excel
1 Answers

In this case use Worksheetfunction.Countif to identify if a range contains the header.
So, replace your Select Case statement with:

If Worksheetfunction.Countif(Range_reference, Cells(1, vtfc)) > 0 Then
    'Do nothing
Else
    Columns(vtfc).Delete
End If

Where I've put Range_reference, replace that with a vba range of wherever you wish to keep the headers list (for example Activeworkbook.Sheets("Headers").Range("A:A"))

user_122319840
Related