Use VBA AutoFilter to filter data in Excel. The VBA AutoFilter method lets you filter multiple fields at once and even filter on more than one criteria.
If you have a big table of data, like employee information, you might need to filter it by certain criteria in order to generate reports.
Today we’ll dive into how to use VBA
AutoFilter to do that. AutoFilter is a nifty little way to perform database-like filtering in Excel. It simply hides the rows that don’t match your criteria so you can ignore irrelevant information.
Although AutoFiltering can be done manually directly in Excel’s GUI, it’s much funner to do it programmatically with VBA. It’s pretty simple and quite practical. Let’s pretend you need to print multiple reports based on the same dataset. How nice would it be to be able to automatically filter and manipulate your data and print all your reports in rapid succession? AutoFilter with VBA makes that possible!
- The Dataset
- Filtering on a Single Column
- Clear AutoFilter VBA
- Filtering a Full Table
- The Arguments
- Filtering on Multiple Fields
We are going to use a small table so you can inspect the results yourself, but if you were working with tens of thousands of entries, you would have to trust your programming skills. That is why it is so important to understand the logic of your code. The longer your code takes to run, the more inconvenient it is to make changes as you go. More importantly, you’re not able to physically inspect tens of thousands of rows of data in any reliable way.
If you’re not confident with your programming abilities, I encourage you to subscribe to my free wellsrPRO VBA Training Program using the form below.
Anyway, this is our dataset of very high-powered employees, though you wouldn’t know it from their salaries and the size of the company.
Table of Employees along with some important information
Using a Single Column as the Criteria
If you know which column you want to filter, you can set the filter on a single column rather than the whole table. Let’s say you want to find people in the marketing department. You could simply filter on column E, because Excel will hide every row that does not fit the criteria.
To use the
AutoFilter method, you will need a base range, which you can declare like this:
Dim range_to_filter As Range Set range_to_filter = Range("E:E")
Now, you can run this code to find everyone in the marketing department:
Sub filter_on_department() Dim range_to_filter As Range Set range_to_filter = Range("E:E") range_to_filter.AutoFilter Field:=1, Criteria1:="Marketing" End Sub
If you prefer positional arguments, you could also write the filtering line like this:
range_to_filter.AutoFilter 1, "Marketing"
Either way, the end result is this:
A table filtered on "Department" only
There are two things to take note of here:
- Excel’s filter is only applied to Column E, so if you need to change things manually, you might need to reapply the filter
- Several rows are hidden, even though we only filtered on Column E
Clear AutoFilter with VBA
If you try to run a second AutoFilter after applying one, like in the example above, VBA will try to stack your filters. You’ll be applying multiple filters to the same dataset. That might be exactly what you you want to do, and we’ll show you a good example of this later, but chances are it’s not what you intended.
If you don’t want to apply multiple filters to the same dataset, you need to clear your filters before applying your new filter. There are a number of ways to do this, but I’ll show you two ways.
Method 1: VBA ShowAllData
Sub clear_filter_vba() On Error Resume Next ActiveSheet.ShowAllData On Error GoTo 0 End Sub
This approach keeps all the filters intact, but it uses the ShowAllData method to show all the data in all your filtered ranged. In other words, it basically turns all your filters into empty filters. A filter is still applied, but it’s just not filtering anything.
Method 2: VBA AutoFilterMode
Sub clear_autofilter_vba() With ActiveSheet If .AutoFilterMode Then .AutoFilterMode = False End If End With End Sub
This method completely disables all the filters on your active sheet. It starts you off with a clean slate!
If you don’t want to apply multiple filters to your data, it’s a good idea to run one these two macros before calling the VBA AutoFilter method. The same advice applies if you’re stepping through this tutorial sequentially. Not clearing your filters may even generate an error.
Filtering on a Full Table
Now that we’ve shown you how to clear your filters, let’s say you want to filter an entire table using the VBA AutoFilter approach. There are (at least) two ways to declare the range for your entire table.
If your tables are thousands of rows long or you just want Excel to filter the entire column, you can use columns as the range:
Set range_to_filter = Range("A:E")
Otherwise, you could set the top left and bottom right corner cells as the range, like this:
Set range_to_filter = Range("A1:E9")
A good reason to do this more limited filtering implementation might be the following scenario, where you have two tables - one on top of the other. This might be a rather poor design scheme, but bear with me.
Two tables in the same columns with a blank row for a separator
Now, using our
range_to_filter = Range("A1:E9") example, we can filter just the first table and leave the second one untouched. If we used
Range("A:E"), we would inadvertently filter the second table, even though it is irrelevant to our search of the first table!
There are five arguments for VBA AutoFilter, all of which are optional. If you don’t enter any arguments, you’ll just be toggling empty filters on and off
- Field - the index of the column you want to filter on, with the leftmost column indexed at one (not zero).
- Criteria1 - the first criterion to filter on, always a string, such as “Marketing” or “>30000” (the exception is when the criteria is a single number, like 20000)
- Operator- an Excel operator that makes fulfilling certain filter requirements easier (like top 10% of the table)
- Criteria2 - used in conjunction with
Criteria1for compound filtering (perhaps you need something between 35000 and 45000)
- VisibleDropDown - TRUE or FALSE argument that either shows or hides the dropdown arrow for the filtered fields
We already saw this above, but there was only one field (Column E), so let’s try when there are multiple fields.
Let’s say you want to filter the table on Base Pay with
The entire code block would look like this:
Sub filter_on_base_pay() Dim range_to_filter As Range Set range_to_filter = Range("A1:E9") range_to_filter.AutoFilter Field:=4, Criteria1:=">45000" End Sub
And the final result is:
Employee table is filtered by base pay, the email/security table is untouched
Apparently our marketing team gets paid well while everyone else does not.
Adding A Second Criterion
Perhaps you want to view a range of salaries, with an upper and lower limit. The AutoFilter
Criteria2 argument is an excellent way to accomplish this:
range_to_filter.AutoFilter Field:=4, Criteria1:=">40000", Criteria2:="<70000"
The Employee table filtered on base pay between $40,000 and $70,000
You can specify several “operators” with you VBA AutoFilter code. Operators do neat things, like filtering the top 10% of your data. Going back to our dataset, let’s say you want to see the highest paid 10% of your employees. You would use the
xlTop10Percent Operator argument when applying your VBA AutoFilter.
range_to_filter.AutoFilter Field:=4, Operator:=xlTop10Percent
And you end up with just Mr. Mozart in your list. Because we only have 8 employees, the Top 10 percent will be 0.8 of a person, which rounds up to one.
You can modify the
Operator argument using
Criteria1, too. This works for the Top and Bottom 10 percent or items options.
For example, you might want the three lowest paid people (so you can give them a raise, of course). To find them, you could use this snippet:
range_to_filter.AutoFilter Field:=4, Criteria1:="3", Operator:=xlBottom10Items
That will leave us with Mr. Smith, Mr. Sejong, and Mr. Nakamoto. All of whom should get raises.
Operator argument connects the
Criteria2 arguments. The default is
xlAnd, the logical AND, so if you have both
Criteria2, the operator will require both to be true to display the row in question.
A creative use of the operator might be to find outliers in your dataset. Perhaps you want to find very high paid or low paid people. In that case, you can run this code:
Sub find_outliers() Dim range_to_filter As Range Set range_to_filter = Range("A1:E9") range_to_filter.AutoFilter Field:=4, Criteria1:="<35000", Criteria2:=">70000", Operator:=xlOr End Sub
leaves us with just Mr. Smith and Mr. Mozart, our lowest and highest paid employees, respectively. As you can see, I didn’t follow the positions set out in
AutoFilter, so I need to name each argument and follow it by :=.
Like I said earlier, AutoFilter operators are pretty neat. Take a look at this table and play around with them!
|xlAnd||Require both Criteria1 and Criteria 2 to be TRUE|
|xlBottom10Items||Display the Bottom 10 Items - can be set to different number of items by using Criteria1|
|xlBottom10Percent||Display the Bottom 10% of Items - also can be set to a different percentage by specifying the desired percentage in Criteria1|
|xlFilterCellColor||Displays only cells with background color determined in Criteria1. (use the RGB() function to get the color's numeric value, no need for a string)|
|xlFilterDynamic||Filters on various preprogrammed criteria, accessible through xlDynamicFilterCriteria: set Criteria1:=xlDynamicFilterCriteria then add a dot and Intellisense will list all the possibilities|
|xlFilterFontColor||Displays only cells with text of the color specified in Criteria1 (again, use the RGB function to get the corresponding number)|
|xlFilterValues||Hides all values that do not appear in a list specified in Criteria1 (use an Array with strings for each entry in your Criteria1 argument)|
|xlFilterNoFill||Hides any cell that has no background fill|
|xlOr||Perform a logical OR on Criteria1 and Criteria2|
|xlTop10Items||Same as Bottom 10 Items, except at the other end|
|xlTop10Percent||Same as Bottom 10 Percent, except at the other end|
Filtering on Multiple Fields
If you want to filter on multiple fields, like finding everyone with either High or Medium security clearance and email beginning with “wm”, you just need to run several
AutoFilter lines over the same range. Each new AutoFilter line will add a new filter, targeting your new criteria.
Sub filter_emails() Dim range_to_filter As Range Set range_to_filter = Range("A11:C15") range_to_filter.AutoFilter field:=2, Criteria1:="High", Criteria2:="Medium", Operator:=xlOr range_to_filter.AutoFilter field:=1, Criteria1:="wm*" End Sub
Notice the asterisk wildcard in the Criteria1 argument. The final outcome looks like this:
Our two tables, with only the bottom sorted on security clearance and email address.
We cautioned about inadvertantly applying multiple filters when we showed you how to clear your VBA filters, but this example proves that sometimes it can be helpful to have multiple filters!
You can use VBA AutoFilter in plenty of situations. Many businesses and teams treat Excel as a kind of makeshift database, and sorting through thousands of entries is what databases are meant for. Using tables in Excel to mimic database queries is practical in adhoc situations or when maintaining a full database just isn’t feasible. I suppose it’s also useful if you don’t want to learn SQL.
AutoFilter will let you search through those tables to find whatever you need much faster, and more reliably, than manually looking line-by-line. The easy implementation in VBA means you can do it programmatically, too, rather than writing a lot of validation code yourself with loops and conditionals.
If you haven’t already done so, please subscribe to my free wellsrPRO VBA Training Program using the form below. What are you waiting for? You’ll love the great VBA content I send your way!
Oh, and if you have a question, post it in our VBA Q&A community.
The best free VBA training on the web
I see people struggling with Excel every day and I want to help. That's why I'm giving away my 90-days to Master VBA eCourse and my entire personal macro library for free.