Important This document may not represent best practices for current development, links to downloads and other resources may no longer be valid. Current recommended version can be found here. ArchiveDisclaimer

Filters Object (Excel)

A collection of Filter objects that represents all the filters in an autofiltered range.

Use the Filters property 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) 
 op = ns 
 c2 = ns 
 End If 
 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 

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
© 2016 Microsoft