Filters object (Excel)
A collection of Filter objects that represents all the filters in an autofiltered range.
Use the Filters property of the AutoFilter object to return the Filters collection. The following example creates a list that contains the criteria and operators for the filters in the autofiltered range on the Crew worksheet.
Dim f As Filter
Dim w As Worksheet
Const ns As String = "Not set"
Set w = Worksheets("Crew")
Set w2 = Worksheets("FilterData")
rw = 1
For Each f In w.AutoFilter.Filters
If f.On Then
c1 = Right(f.Criteria1, Len(f.Criteria1) - 1)
If f.Operator Then
op = f.Operator
c2 = Right(f.Criteria2, Len(f.Criteria2) - 1)
Else
op = ns
c2 = ns
End If
Else
c1 = ns
op = ns
c2 = ns
End If
w2.Cells(rw, 1) = c1
w2.Cells(rw, 2) = op
w2.Cells(rw, 3) = c2
rw = rw + 1
Next
Use Filters (index), where index is the filter title or index number, to return a single Filter object. The following example sets a variable to the value of the On property of the filter for the first column in the filtered range on the Crew worksheet.
Set w = Worksheets("Crew")
If w.AutoFilterMode Then
filterIsOn = w.AutoFilter.Filters(1).On
End If
Have questions or feedback about Office VBA or this documentation? Please see Office VBA support and feedback for guidance about the ways you can receive support and provide feedback.