Class Filter (Excel VBA)

The class Filter represents a filter for a single column.

Item

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

Dim flt as Filter
Set flt = ActiveSheet.AutoFilter.Filters(Index:=1)

Filter class variables can be set using the Filters.Item procedure.

For Each

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

Dim flt As Filter
For Each flt In ActiveSheet.AutoFilter.Filters
	
Next flt

Count

Returns the number of objects in the collection.

Dim lngCount As Long
lngCount = ActiveSheet.AutoFilter.Filters(1).Count

Criteria1

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

Dim varCriteria1 As Variant
varCriteria1 = ActiveSheet.AutoFilter.Filters(1).Criteria1

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.

On

True if the specified filter is on.

Dim booOn As Boolean
booOn = ActiveSheet.AutoFilter.Filters(1).On

Operator

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

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).
ActiveSheet.AutoFilter.Filters(1).Operator = xlAnd

Filters.Count

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

Dim lngCount As Long
lngCount = ActiveSheet.AutoFilter.Filters.Count

Filters.Item

Returns a single object from a collection.

Item (Index)

Index: The index number of the object.

Dim fltItem As Filter
Set fltItem = ActiveSheet.AutoFilter.Filters(Index:=1)