expression.AutoFilter(Field, Criteria1, Operator, Criteria2, VisibleDropDown)

Code examples relate to this database.

Filter on a Single Criteria

This code would filter the data so that only cash payments were displayed.

Range("A1").CurrentRegion.AutoFilter Field:=6,Criteria1:="CASH"

Filter on Two “Or” Criteria

Use the xlOr operator to perform “Or” criteria.

Range("A1").CurrentRegion.AutoFilter _
Field:=6, Criteria1:="CASH", Operator:=xlOr,Criteria2:="VOUCH"

Filter on More Than Two “Or” Criteria Using Array

Range("A1").CurrentRegion.AutoFilter _
Field:=4, Criteria1:=Array("1", "20","30", "40"), Operator:=xlFilterValues

NB You have to use the xlFilterValues operator when using an Array as your criteria.

Filter on Two “And” Criteria

Use the xlAnd operator to perform “And” criteria.

Range("A1").CurrentRegion.AutoFilter _
Field:=5,Criteria1:=">=5000", Operator:=xlAnd,Criteria2:="<=6000"

Filter on Top/Bottom X Values

Use the Criteria parameter to specify the number of records to return.

Range("A1").CurrentRegion.AutoFilter _
Field:=5, Criteria1:=25, Operator:=xlTop10Items

Perform Dynamic Date Filters

Excel’s Autofilter allows you to apply date filters that for example filter for dates in the current month, quarter or year, as well as filters for past and future periods. These can be accessed in VBA. You will need to use xlFilterDynamic as your Operator. The following code filters the date field for dates in the current month. Use CTRL SPACE to open the IntelliSense list which includes all the dynamic filter names.

Range("A1").CurrentRegion.AutoFilter field:=2, Criteria1:=xlFilterAllDatesInPeriodMonth, Operator:=xlFilterDynamic

Perform Dynamic Average Filters

This code filters the TRANS_VALUE column for the above average values.

Range("A1").CurrentRegion.AutoFilter _
Field:=5,Criteria1:=xlFilterAboveAverage, Operator:=xlFilterDynamic

Perform a Filter Based on Cell Colour

Range("A1").CurrentRegion.AutoFilter _
Field:=1, Criteria1:=RGB(255, 255, 0),Operator:=xlFilterCellColor

Perform a Filter Based on Icon

This code filters for cells containing a red traffic light.

Range("A1").CurrentRegion.AutoFilter _
Field:=1, Criteria1:=ActiveWorkbook.IconSets(xl3TrafficLights1).Item(1), _
Operator:=xlFilterIcon

Perform Wildcard Filters on Text Fields

You can use the * and ? wildcard characters in the usual way.

Range("A1").CurrentRegion.AutoFilter field:=3,Criteria1:="ADC-AA?*"

To apply filters to more than one field, you could do this…

WithRange("A1").CurrentRegion
   .AutoFilter Field:=6,Criteria1:="CASH", Operator:=xlOr, Criteria2:="VOUCH"
   .AutoFilter Field:=4, Criteria1:=Array("1", "20","30", "40"), Operator:=xlFilterValues
End With