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
- Defining the Filter Criteria
- Clearing Filters
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.
.AutoFilter method operates on Range objects, which both
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
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
.AutoFilter method has a parameter
Field. This comes in handy when your
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
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:
Filter For a String
Let’s say you want to find all orders from the
Range("E:E").AutoFilter Field:=1, Criteria1:="North"
Note the filter is applied only to Column
Filter For Multiple Strings
What if we wanted all data from the North and the West regions? This is where
Operator comes in. The
Operator parameter accepts, among others,
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
Range("E:E").AutoFilter Field:=1, Criteria1:="North", Criteria2:="West", Operator:=xlOr
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.
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.
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
To find all orders over 500 - perhaps for expedited shipping - use the
Criteria1 parameter and set it equal to
Range("G:G").AutoFilter Field:=1, Criteria1:=">500"
Operator pramaters are not necessary since we are filtering the
We’ve talked about
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
'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
Operator parameter also has an
xlTop10Items option that restricts the response to X number of items, where X is defined using the
'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
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.