Class Filter (Excel VBA)

The class Filter represents a filter for a single column. 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)

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. Here you can find possible values for XlAutoFilterOperator.

ActiveSheet.AutoFilter.Filters(1).Operator = xlAnd