Criteria for VBA AutoFilters

How to use AutoFilter Criteria in Excel VBA

Got any Excel/VBA Questions? Free Excel Help

In summary, rather than check if AutoFilters are already applied to a specified Worksheet with an IF Statement, then, if they were on and in use (filtered down) we would turn them off and apply to the needed range. If they weren't on then simply apply them to the needed range.

This however was a lot of superfluous code. The easiest and best way is as shown below:

Sub ApplyAutoFilters()



With ActiveSheet

            .AutoFilterMode = False

            .Range("A1:D1").AutoFilter

    End With

    

End Sub

In the code above we turn off any existing AutoFilters and apply them to the range A1:D1 of the active worksheet.

FILTERING DOWN TO SHOW 1 MATCHING CRITERIA

Let's now look at how we can apply AutoFilters and show only matching criteria. In the examples below I have used a specified Worksheet by referencing its codename . It is also based on the data being in the range A1:D100 with A1:D1 being headings:

Name | Age | Date Joined | Department

Sub FilterTo1Criteria()



With Sheet1

            .AutoFilterMode = False

            .Range("A1:D1").AutoFilter

            .Range("A1:D1").AutoFilter Field:=2, Criteria1:=35

    End With

    

End Sub

In the example below we have filtered our table down to match 1 criteria (Criteria1) on our second heading (Age) to show only those who are 35. If we were to show all those that are 35 or older our Criteria1 would need to be like:

Criteria1:=">=35"

In other words, the criteria and any operators should be passed as text with an equal sign preceding the string.

We can have the filter show only blanks for the specified Field by using: Criteria1:="="

To show all non-blanks we would use: Criteria1:="<>"

XlAutoFilterOperator can be one of these constants

xlAnd default
xlBottom10Items
xlBottom10Percent
xlOr
xlTop10Items
xlTop10Percent

If we wanted to show only those in the Name field whose name Start s with a "D" we would use: Criteria1:="=D*"
To show all names that do not contain a letter "a" we would use: Criteria1:="<>*a*"

In short, the best way to obtain your needed criteria is to simply record a macro filtering your table down and then copy the Criteria1: and the optional Criteria2: code generated.

If desired, for whatever reason, we can have Excel hide the Filter arrow for Field2 (or any Field) by using an additional
argument after Criteria1. That is: ,VisibleDropDown:=False

FILTERING DOWN TO SHOW 2 MATCHING CRITERIA

Let's now expand on the above by filtering down to show 2 criteria.

Sub FilterTo2Criteria()



With Sheet1

            .AutoFilterMode = False

            .Range("A1:D1").AutoFilter

            .Range("A1:D1").AutoFilter Field:=2, Criteria1:=">=35", _

             Operator:=xlAnd, Criteria2:="<=45"

    End With

    

End Sub

In the above code we have chosen to show all whose age is between 35 and 45. It's important to note that for the Operator argument we have used xlAnd. If we had used the other choice (XlOr) our results would be that of our original table. That is, all records would show as all people would be either >=35 or <=45.

Sub FilterTo2Fields()



With Sheet1

            .AutoFilterMode = False

                With .Range("A1:D1")

                     .AutoFilter

                     .AutoFilter Field:=1, Criteria1:="Dave"

                     .AutoFilter Field:=4, Criteria1:="Lab"

                End With

    End With

    

End Sub

In the code above we have shown all those with the name "Dave" whose department is "Lab". As you can see from the above code,
We can add more fields, but cannot exceed our total column count of headings. In this case we could use Field 1, 2, 3 and/or 4.

FILTERING DOWN TO SHOW WILDCARDS

The wildcard characters we can use in AutoFilter are the asterisk (*) to represent a string of characters and/or the question mark (?) to represent a single character.

However, what if we need to show data that actually houses the * or ? By the way, if at all possible these characters should not be used on their own.

Sub FilterToShowAsterisk()



With Sheet1

            .AutoFilterMode = False

            .Range("A1:D1").AutoFilter

            .Range("A1:D1").AutoFilter Field:=1, Criteria1:="~*"

    End With

    

End Sub

As you can see from the above code, we have told Excel we actually want to filter by the asterisk and not have it seen as a wildcard. The same applies for the question mark. That is: Criteria1:="~?"

Microsoft ® and Microsoft Excel ® are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft