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 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;
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
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:="~?"
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. ALL purchases totaling over $150.00 gets you BOTH! Purchases MUST be made via this site. Send payment proof to firstname.lastname@example.org 31 days after purchase date.
Instant Download and Money Back Guarantee on Most Software
Excel Trader Package Technical 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
Some of our more popular products are below...
Convert Excel Spreadsheets To Webpages | Trading In Excel | Construction Estimators | Finance Templates & Add-ins Bundle | Code-VBA | Smart-VBA | Print-VBA | Excel Data Manipulation & Analysis | Convert MS Office Applications To...... | Analyzer Excel | Downloader Excel | MSSQL Migration Toolkit | Monte Carlo Add-in | Excel Costing Templates