VBA Advanced Filters: Automate Filtering Specific Values
It’s pretty straight forward to filter down on specific values in your data with VBA code. You just list out the values in an array like so:
Sub FilterOnValues()
'PURPOSE: Filter on specific values
'SOURCE: www.TheSpreadsheetGuru.com
Dim rng As Range
Set rng = ActiveSheet.Range("B7:D18")
FilterField = WorksheetFunction.Match("Country", rng.Rows(1), 0)
'Turn on filter if not already turned on
If ActiveSheet.AutoFilterMode = False Then rng.AutoFilter
'Filter Specific Countries
rng.AutoFilter Field:=FilterField, Criteria1:=Array( _
"Russia", "China", "Greece", "France"), Operator:=xlFilterValues
End SubBut what if you have 100 values you want to filter on? Sometimes it’s more efficient to filter out values because there are fewer excluded items than included. Unfortunately, this functionality is not as straightforward as it should be in VBA.
The only way I know to filter out values with VBA is to use Excel’s spreadsheet-based Advanced Filter functionality. This requires you to set up a small data table on your spreadsheet and read-in the criteria with your VBA code. Let’s look into how you can create this very powerful table and take VBA filtering to the next level!
The Advanced Filter Table Setup
The setup of for an Advanced Filter table is vital to properly filter your data just the way you want. Each heading in your designated table (or range if you choose) must match verbatim to the column headings in your data set. Note that you can repeat column names to apply multiple criteria to a single column in your data (do not use an Excel table if you need to do this since tables must have unique column headings).
AND Filters
Any criteria written in the same row in your filter table is treated as an AND statement. You can make your Advanced Filter table as wide as you need to handle multiple criteria. The below example would handle filtering out 4 country names within the Country column of your data set.
OR Filters
Each new row in your Advanced Filter table designates an OR statement. This can be handy if you want to narrow down your data to include one of a few values. In the below example, the advanced filter range would show and row that contained “Maria”, “Russia”, “France”, or a last name that starts with the letter “T”.
Based on the above criteria, you can see below how the table would filter the data. The highlighted words are what the Advanced Filter flagged as data the user wanted to see.
Notice in the Last Name column, that any name that begins with the letter “T” gets flagged. This is because a wildcard symbol was used in the Advanced Filter range. By placing an asterisk (*) after the letter “T”, you can filter on words that begin with the characters prior to the asterisk.
How To Use Advanced Filtering With VBA
It is very easy to link up your Advanced Filter range into your VBA code. I recommend using a dynamically named range or a table to store your filtering criteria.
Sub AdvancedFilter()
Dim rng As Range
'Set variable equal to data set range
Set rng = ActiveSheet.Range("B8:D19")
'Filter out certain values (filter indicators will not appear!)
rng.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
ActiveSheet.ListObjects("Table4").Range, Unique:=True
End SubThis Is Better Than Writing Criteria Inside Your Code
When I began coding in VBA, I would always write data into my code. It was not until I had to train others to use and modify my automations, that I learned very quickly the power of deriving settings and criteria from a user interface (aka spreadsheet) instead of going in and re-writing the VBA code itself.
For example, it would be much easier to tell a co-worker (let’s call her Susan) who has no experience with VBA coding, how to modify an advanced filter table if sometime down the road a new country needs to be filtered on. Introducing Susan to VBA and overwhelming her with lines of a foreign language would certainly be more difficult to explain, and my confidence that she could modify the VBA on her own would not be very high. Save yourself from future headaches and start storing values and settings in a worksheet instead of in your VBA code.
Learn More With This Example Workbook
I have created a sample workbook with 4 different scenarios for handling different types of filtering needs. I also switch up how the code is written by pulling from spreadsheet ranges and Excel tables. The workbook and its code are completely unlocked so you can dig in and discover how the magic works. Hopefully, you start saving some time and automating the filtering of your data with the techniques featured in this article.
If you would like to get a copy of the Excel file I used throughout this article, feel free to directly download the spreadsheet by clicking the download button below.
After 10+ years of creating macros and developing add-ins, I've compiled all the hacks I wish I had known years ago!
Keep Learning
Chris Newman
Chris is a finance professional and Excel MVP recognized by Microsoft since 2016. With his expertise, he founded TheSpreadsheetGuru blog to help fellow Excel users, where he shares his vast creative solutions & expertise. In addition, he has developed over 7 widely-used Excel Add-ins that have been embraced by individuals and companies worldwide.