Use Advanced Filters With VBA To Automate Filtering On and Out Specific Values

Advanced Filtering With Excel VBA

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

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 Sub

But 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).

How To Set up An Advanced Filter With VBA Macro Code

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.

How To Filter Out Specific Values With VBA Macros in Excel

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".

Writing An Or Statement With VBA and Advanced Filters

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.

Microsoft Excel Advanced Filtering Automation

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 Sub

This 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 headache 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 automate filtering your data with the techniques featured in this article.

As always, in order to download this example file you will need to be a subscriber of my free newsletter. If you click the green button below you can easily sign up and you will be emailed the password to get into the subscribers-only area of this website.

     Already Subscribed? Click HERE to log-in to the "Example Files" section

     Already Subscribed? Click HERE to log-in to the "Example Files" section


How Do I Modify This To Fit My Specific Needs?

Chances are this post did not give you the exact answer you were looking for. We all have different situations and it's impossible to account for every particular need one might have. That's why I want to share with you: My Guide to Getting the Solution to your Problems FAST! In this article, I explain the best strategies I have come up with over the years to getting quick answers to complex problems in Excel, PowerPoint, VBA, you name it

I highly recommend that you check this guide out before asking me or anyone else in the comments section to solve your specific problem. I can guarantee 9 times out of 10, one of my strategies will get you the answer(s) you are needing faster than it will take me to get back to you with a possible solution. I try my best to help everyone out, but sometimes I don't have time to fit everyone's questions in (there never seem to be quite enough hours in the day!).

I wish you the best of luck and I hope this tutorial gets you heading in the right direction!

Chris "Macro" Newman :)