Home Excel VBA. Toggle Column Filter by using Column Name instead of Column Index

Questions

Excel VBA. Toggle Column Filter by using Column Name instead of Column Index

November 12, 2021

I want to use a button that runs a VBA code that will toggle the filter in a Table Column on and off.

This is what I have so far:

Sub ToggleStatusFilter()

  With ActiveSheet.ListObjects("Table4")
    If .AutoFilter.Filters.Item(23).On Then
      .Range.AutoFilter Field:=.ListColumns("Status").INDEX
    Else
      .Range.AutoFilter Field:=.ListColumns("Status").INDEX, Criteria1:="Completed"
    End If
  End With
End Sub

This code works for now. But what I want is to be able to replace Item(23) in the code with the name of the Column so that when new columns are added or some old columns are deleted from the table and the position of the Column changes, the code will still work without needing to edit it.

Is there a way this can be achieved? Any help will be much appreciated.

>Solution :

Perhaps using ListColumn.Index (again):

With ActiveSheet.ListObjects("Table4")
    Dim indx As Long
    indx = .ListColumns("columnHeader").Index

    If .AutoFilter.Filters.Item(indx).On Then