<<Convert Excel Spreadsheets to Web Pages \| Trading Software That Operates Within Excel \| Convert Excel, Access & Other Databases \| Merge Excel Files>>

Criteria for VBA AutoFilters

TRY OUT: Smart-VBA | Code-VBA | Analyzer-XL | Downloader-XL | Trader-XL| More Free Downloads.. Best Value: Finance Templates Bundle

How to use AutoFilter Criteria in Excel VBA

Got any Excel Questions? Free Excel Help

Back To: AutoFilters in Excel VBA.See Also: AutoFilters via User Interface | Display Excel AutoFilter Criteria

Ok, last page we looked at how we can set AutoFilters up within an Excel Workbook. 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 itsCodeName . 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 optionalCriteria2: 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 usedxlAnd. 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:="~?"

Back To: AutoFilters in Excel VBA

Next: AutoFilter Date Criteria in VBA

Excel Dashboard Reports & Excel Dashboard Charts 50% Off Become an ExcelUser Affiliate & Earn Money

Special! Free Choice of Complete Excel Training Course OR Excel Add-ins Collection on all purchases totaling over $64.00. ALLpurchases totaling over $150.00 gets you BOTH! Purchases MUST be made via this site. Send payment proof to [email protected] 31 days after purchase date.

Instant Download and Money Back Guarantee on Most Software

Try out:Analyzer XL |Downloader XL |Smart VBA |Trader XL Pro (best value) |ConsoXL | MergeXL | O2OLAP for Excel | MORE>>

Excel Trader PackageTechnical Analysis in Excel With $139.00 of FREE software!

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