Excel VBA to Remove Columns Based on Multiple Headers on Multiple Sheets

Question

Would the below code be able to be modified to 1 loop through all sheets in a workbook and 2 remove multiple columns based on their headers?

example: "status","Status Name","Status Processes" etc.)? And then cycle through all sheets in the wkbk to do the same checks?

Sub remove_columns()
    For i = ActiveSheet.Columns.Count To 1 Step -1
        If InStr(1, Cells(1, i), "Status") Then Columns(i).EntireColumn.Delete
    Next i
End Sub

Answer 1

dim a as long, w as long, vDELCOLs as variant, vCOLNDX as variant
vdelcols = array("status","Status Name","Status Processes")
with thisworkbook
    for w=1 to .worksheets.count
        with worksheets(w)
            for a=lbound(vdelcols) to ubound(vdelcols)
                vcolndx=application.match(vdelcols(a), .rows(1), 0)
                if not iserror(vcolndx) then
                    .columns(vcolndx).entirecolumn.delete
                end if
            next a
        end with
    next w
end with

You obviously have less columns to delete than columns that exist. Look for matches to the columns to delete rather than comparing every column to the delete list.

This looks (case-insensitive) for the column names in row 1.