Excel VBA AutoFilter to Filter Data Table

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
  • VBA ShowAllData
  • VBA AutoFilterMode
• Filtering a Full Table
• The Arguments
  • Example with Criteria1 and Field
  • The Operator Argument
• Filtering on Multiple Fields
• Conclusion

Our Dataset

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:

1. Excel’s filter is only applied to Column E, so if you need to change things manually, you might need to reapply the filter
2. 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!

The Arguments

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 Criteria1 for 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

Example with Field and Criteria1

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 Range = ("A1:E9"). In that case, “Base Pay” is the fourth field. Our criterion will be base pay greater than $45,000.

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

The Operator Argument

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.

Connecting Criteria1 and Criteria2

Technically, the Operator argument connects the Criteria1 and Criteria2 arguments. The default is xlAnd, the logical AND, so if you have both Criteria1 and 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!

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!

Conclusion

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!