Class Filter (Excel VBA)

The class Filter represents a filter for a single column.

Set

To use a Filter class variable it first needs to be instantiated, for example

Dim lngIndex As Long: lngIndex = 
Dim afr As AutoFilter: Set afr = 
Dim fltFilter As Filter
Set fltFilter = afr.Filters(Index:=lngIndex)

The following procedures can be used to set variables of type Filter: Filters.Item and AutoFilter.Filters

For Each

Here is an example of processing the Filter items in a collection.

Dim afr As AutoFilter: Set afr = 
Dim fltFilter As Filter
For Each fltFilter In afr.Filters
	
Next fltFilter

Count

Returns the number of objects in the collection.

Criteria1

Returns the first filtered value for the specified column in a filtered range.

With Worksheets("Crew") 
 If .AutoFilterMode Then 
 With .AutoFilter.Filters(1) 
 If .On Then c1 = .Criteria1 
 End With 
 End If 
End With

Criteria2

Returns the second filtered value for the specified column in a filtered range.

If you try to access the Criteria2 property for a filter that does not use two criteria, an error will occur. Check that the Operator property of the Filter object doesn't equal zero (0) before trying to access the Criteria2 property.

With Worksheets("Crew") 
 If .AutoFilterMode Then 
 With .AutoFilter.Filters(1) 
 If .On And .Operator Then 
 c2 = .Criteria2 
 Else 
 c2 = "Not set" 
 End If 
 End With 
 End If 
End With

On

True if the specified filter is on.

With Worksheets("Crew") 
 If .AutoFilterMode Then 
 With .AutoFilter.Filters(1) 
 If .On Then c1 = .Criteria1 
 End With 
 End If 
End With

Operator

Returns an XlAutoFilterOperator value that represents the operator that associates the two criteria applied by the specified filter.

Possible Values are xlAnd - Logical AND of Criteria1 and Criteria2, xlBottom10Items - Lowest-valued items displayed (number of items specified in Criteria1), xlBottom10Percent - Lowest-valued items displayed (percentage specified in Criteria1), xlFilterAutomaticFontColor, xlFilterCellColor - Color of the cell, xlFilterDynamic - Dynamic filter, xlFilterFontColor - Color of the font, xlFilterIcon - Filter icon, xlFilterNoFill, xlFilterNoIcon, xlFilterValues - Filter values, xlOr - Logical OR of Criteria1 or Criteria2, xlTop10Items - Highest-valued items displayed (number of items specified in Criteria1), xlTop10Percent - Highest-valued items displayed (percentage specified in Criteria1).

Parent

Returns the parent object for the specified object. Read-only.

Filters.Count

Returns a Long value that represents the number of objects in the collection.

Filters.Item

Returns a single object from a collection.

Syntax : expression.Item (Index)

Index: The index number of the object.

Set w = Worksheets("Crew") 
If w.AutoFilterMode Then 
 filterIsOn = w.AutoFilter.Filters.Item(1).On 
End If

Filters.Parent

Returns the parent object for the specified object. Read-only.