Filtering hides rows from a table. Filtering data is a foundational component in understanding large datasets. Some applications only need filtering on one column while others may be more complex. This article will explore filtering on a single column, but we have helpful tutorials on [applying multiple filters with VBA AutoFilter][how to apply multiple filters] and sorting columns using VBA.

The Filter Range

The first thing to ask yourself when filtering is “what range should I filter?” The .AutoFilter method filters only one column at a time. To filter multiple columns requires an iteration loop or the use of the .AdvancedFilter method, which we touched on in our article about filtering unique values.

The .AutoFilter method operates on Range objects, which both Range and Columns objects satisfy. Since we want to filter an entire column, we can use either of these lines:

Columns(1).AutoFilter       'to filter Column 1 (same as A)
Range("A:A").AutoFilter     'to filter Column A (same as 1)

If you want to filter a defined range rather than an entire column, specify so in your initial Range object: Range("A1:A300").AutoFilter. This allows you to filter up to a certain row, leaving other rows alone.

A Word on Terminology

Excel filters apply across entire rows! You cannot filter only some columns, because filtering hides the entire row. Filtering some columns but not others would break the integrity of the data, erasing the relational connections between columns.

To explain what we mean, try visualizing this scenario. Let’s say we have First Names in column A and Last Names in column B. It wouldn’t make sense to filter only rows in A because then first and last names would no longer be matched appropriately.

Therefore, we usually speak of filtering ON a column, meaning we can look for all first names that start with J, but we cannot filter only Column A without hiding values in B. The phrase filter on [target] implies we use the target as the criteria, but we still hide rows across the entire sheet.

The Field Parameter

The .AutoFilter method has a parameter Field. This comes in handy when your Range spans multiple columns. For example,

Range("B:E").AutoFilter Field:=3    'filters the third column, Column D
Range("B:E").AutoFilter Field:=1    'filters the first column, Column B

When the Range object consists of only one column, such as Columns(3).AutoFilter, the Field parameter is still required - you’d just set it to 1.

Defining the Filter Criteria

Filters are based on two Criteria parameters and a set of special Excel operators, for a total of 3 parameters. Only a single criteria is required to filter, but all three can be used to filter a single column.

You can practice using this CSV, which contains this dataset:

Excel Dataset for Filtering Columns

Filter For a String

Let’s say you want to find all orders from the North region. We can filter the Region column like this:

Range("E:E").AutoFilter Field:=1, Criteria1:="North"

which yields

VBA AutoFilter with String Criteria

Note the filter is applied only to Column E, as indicated by the filter icon at the top of column E.

Filter For Multiple Strings

What if we wanted all data from the North and the West regions? This is where Criteria2 and Operator comes in. The Operator parameter accepts, among others, xlAnd and xlOr. In mathematics terms, AND intersects and OR unions. In non-technical terms, AND requires both criteria to be true, while OR requires only one (but at least one).

So, to filter for all North and all West records, we could use this code:

Range("E:E").AutoFilter Field:=1, Criteria1:="North", Criteria2:="West", Operator:=xlOr

Filtered for North or West
Filtered for North or West

You can even use wildcards (the * symbol) in strings. For example, if you wanted to find all first names that started with F or G, you could use

Range("B:B").AutoFilter Field:=1, Criteria1:="F*", Criteria2:="G*", Operator:=xlOr

If you wanted cities that started with a C and end with an S, you could use xlAnd to force both conditions to be true, like we do in this example:

Range("C:C").AutoFilter Field:=1, Criteria1:="C*", Criteria2:="*S", Operator:=xlAnd

This would yield cities like Columbus and Colorado Springs. Except not in this dataset, because Colorado was not yet in existence when the Declaration of Independence was signed, and this list is mostly signatories of that document.

You may have noticed Sun Tzu (who didn’t live in Ohio or sign the Declaration of Independence) appearing 4 times with exactly the same data. Duplicates like this are common issues in data science, so we made a guide showing you how to filter duplicates (using unique records), too.

Make powerful macros with our free VBA Developer Kit

It’s easy to copy and paste a macro like this, but it’s harder make one on your own. To help you make macros like this, we built a free VBA Developer Kit and wrote the Big Book of Excel VBA Macros full of hundreds of pre-built macros to help you master file I/O, arrays, strings and more - grab your free copy below.

I'll take a free VBA Developer Kit

Filter For Values

Business revolves around numbers, so surely we can filter on more than just strings? That’s right, we can also filter on values, like the Amount values in Column G.

To find all orders over 500 - perhaps for expedited shipping - use the Criteria1 parameter and set it equal to ">500":

Range("G:G").AutoFilter Field:=1, Criteria1:=">500"

The Criteria2 and Operator pramaters are not necessary since we are filtering the Amount column on only one criterion.

Special Filters

We’ve talked about xlAnd and xlOr but the Operator parameter also enables some special filters, like xlTop10Percent. This will display the top 10% of the values in a field, so in a dataset of 50, you’ll get 5 and in a dataset of 1500, you’ll get 150.

Although the name of the operator is xlTop10Percent, you can adjust the actual percentage using Criteria1:

'filter on Amount column (G)
Range("G:G").AutoFilter Field:=1, Criteria1:=5, Operator:=xlTop10Percent        'top 5 percent
Range("G:G").AutoFilter Field:=1, Criteria1:=25, Operator:=xlTop10Percent       'top 25 percent

The Operator parameter also has an xlTop10Items option that restricts the response to X number of items, where X is defined using the Criteria1 parameter.

'filter on Amount column (G)
Range("G:G").AutoFilter Field:=1, Criteria1:=5, Operator:=xlTop10Items          'top 5 items
Range("G:G").AutoFilter Field:=1, Criteria1:=10, Operator:=xlTop10Items         'top 10 items

Leverage User Input

One neat thing about filtering is your filters don’t need to be hardwired. You can ask the user for input since the Criteria parameters accept variables. This example simply asks for a region name in a message box, but with a bit of work on userforms you could design a nice dashboard with many business-specific options and constraints.

Sub filterOnRegion()

Dim regionName As String
regionName = InputBox("Enter the Region name")
Range("E:E").AutoFilter Field:=1, Criteria1:=regionName

End Sub

Clearing Filters

Filters cannot be applied independently using .AutoFilter. Multiple criteria can be applied and on different columns, but each new line of AutoFilter intersects the results - that is, each new line will only “see” the results of the previous filter. When you’re testing, this may cause runtime errors and plenty of frustration. Keep this in your Immediate Window (shortcut Ctrl+G) to quickly reset the filters while testing your code:

ActiveSheet.AutoFilterMode = False

If you need multiple filters, check out our guide on how to properly apply multiple filters.

As you can see, filtering a column in VBA is pretty easy, and you can even add some additional criteria without issue. You can filter on both numeric amounts and strings, and you can apply mathematical relations (like greater than). Excel also provides some built-in dynamic filtering, like xlTop10Percent, depending on your dataset

Just keep in mind that AutoFilter only applies to one column at a time. You can filter multiple columns using it, but it requires multiple lines of code. For more VBA tutorials like this, subscribe using the form below.