VBA Advanced Filter is one of the many hidden gems that Excel VBA offers to make our time more productive.

VBA Advanced Filter requires very little code, is one of the fastest ways to copy data, and provides advanced filtering options that we cannot get anywhere else.

 

VBA Advanced Filter Quick Guide

Using the criteria with AdvancedFilter is very powerful. You can see the possible options in the table below:

 

Task	Cell formula	Examples where true
Contains	Pea ="Pea" ="*Pea*"	Peach, Pea, Appear
Does not contain	="<>*Pea*"	any text that does not contain Pea
Exact match	="=Pea"	Pea
Does not exactly match	="<>Pea"	Peach, Pear etc.
Starts with	="=Pea*"	Peach, Pear, Pea
Ends with	="=*Pea"	SweetPea, GreenPea
Use the ? symbol to represent any single character	="=Pea?"	Pear, Peas or any 4 letter word starting with "Pea"
Any of the symbols *?~	="=Pea~*"	Pea*, Pea?
Case sensitive(see section Using Formulas as Criteria)	=EXACT(A7,"Peach")	Peach
Greater than	=">700"	701,702 etc.
Greater than or equals	=">=700"	700, 701,702 etc.
Less than	="<700"	699,698 etc.
Less than or equals	="<=700"	700, 699, 698 etc.
Equals	="=700"	700

 

Important Note: A Criteria column header must exist as a List range column header. For example, if the Criteria column header is “Fruit” then there must be a List range column header called “Fruit”.

Here are some important things to know about the Criteria column headers:

• They can be used in any order.
• You can use the same header multiple times(see section Advanced Filter Multiple Criteria below).
• You don’t need to include a column header in the criteria if you are not filtering by this column.

What is Advanced Filter

Advanced Filter is a tool that is available in the Sort & Filter  section of the Data tab on the Excel Ribbon:

 

It allows us to filter data based on a Criteria range. This allows us to do more advanced filtering than the standard filtering on the worksheet.

A second advantage of using Advanced Filter is that we have the option to copy the results to a new range if we choose.

Using Advanced Filter is quite simple as you can see from the dialog:

 

We filter in-place or we copy to another location.  We then simply need the data range(List range) and the Criteria Range. If we decided to copy to another location then we provide the “Copy to” range.

Using Advanced Filter is very useful in VBA because it is extremely fast, powerful and as we will see it requires very little code.

VBA Advanced Filter on YouTube

To see me working with Advanced Filter, check out this YouTube video:

 

VBA Advanced Filter Parameters

The following table shows the parameters of the AdvancedFilter function:

Parameter	Optional	Type	Details
Action	Required	xlFilterAction	xlFilterInPlace or xlFilterCopy.
CriteriaRange	Optional	Range	Range of the criteria used for filtering the data.
CopyToRange	Optional	Range	Destination range if Action is set to xlFilterCopy.
Unique	Optional	Boolean	True for unique records only.

You can read about the parameters on the Microsoft help page.

AdvancedFilter requires three ranges to run(or two if you are using xlFilterInPlace as the Action parameter):

1. List range – data to filter.
2. Criteria range – how to filter.
3. Copy To range – where to place the results if the Action parameter is set to xlFilterCopy is set.

AdvancedFilter is a range Function. This means you get the range of data you wish to filter and then call the AdvancedFilter function of that range:

 DataRange.AdvancedFilter Filter Action, Criteria, [CopyTo], [Unique]

 

We can filter in place or we can copy the filter results to another location. This means there are two ways to use AdvancedFilter:

 ' Filter in place
 rgData.AdvancedFilter xlFilterInPlace, rgCriteriaRange

 ' Filter and copy data
 rgData.AdvancedFilter xlFilterCopy, rgCriteriaRange, rgDestination

 

The first parameter indicates the way to apply the filter:

1. xlFilterInPlace – Filter the original data.
2. xlFilterCopy – Copy the filter results to a new range.

If we use xlFilterInPlace then we don’t need the destination range.

 

To remove duplicate records we simply set the Unique parameter to True. Otherwise, duplicate records are ignored:

 ' Filter in place
 rgData.AdvancedFilter xlFilterInPlace, rgCriteriaRange, , True 

 ' Filter and copy data
 rgData.AdvancedFilter xlFilterCopy, rgCriteriaRange, rgDestination, True 

 

Understanding the Advanced Filter Ranges

The following screenshot shows an example of the 3 ranges. The List(or data) range is shaded blue, the Criteria range is green, and the CopyTo range is yellow:

 

 

The following subsection provides a quick guide to each of the ranges:

Criteria Range

• The criteria headers must be one of the List Range column headers. If not then it will be ignored.
• Criteria headers can be in any order.
• You can include as many or as few Criteria headers as you need.
• You can use the same header multiple times – this allows us to do multiple AND operations on the same column.

CopyTo Range

• This range is only used when the Action parameter is set to xlFilterCopy.
• To avoid errors this range should be the Header row of the output destination.
• You can use any(or all) columns from the List range as your output and they can be in any order.
• The columns headers in this range must be a List Range column header or you will get a VBA Runtime Error 1004.

List Range

• The List Range is the range of data that will be filtered.
• You must include the headers as part of the List Range.
• If you set the Action parameter to xlFilterInPlace then the List data will be filtered.
• If you set the Action parameter to xlFilterCopy then the results will be copied to the location which is specified in the CopyToRange parameter.

 

Writing the VBA Code

The easiest way to define the data range is to use CurrentRegion although you can get the range any way you like.  Using CurrentRegion gets all the adjacent data to the specified cell or range.

We can use CurrentRegion like this:

 Dim rgData As Range, rgCriteriaRange As Range
 Set rgData = Range("A4").CurrentRegion
 Set rgCriteriaRange = Range("A1").CurrentRegion

 

To set the CopyTo range, you specify the entire heading row.

You can use a simple trick with CurrentRegion to get the CopyToRange header row. First, use CurrentRegion and then take the first row of the resulting range:

 Dim rgCopyToRange As Range
 Set rgCopyToRange = shFruit.Range("E4").CurrentRegion.Rows(1)

 

The full VBA Advanced filter code looks like this:

Sub RunAdvancedFilter()

    ' Declare the variables
    Dim rgData As Range, rgCriteriaRange As Range, rgCopyToRange As Range
    
    ' Set the ranges
    Set rgData = Sheet1.Range("A4").CurrentRegion
    Set rgCriteriaRange = Sheet1.Range("A1").CurrentRegion
    Set rgCopyToRange = Sheet1.Range("D1").CurrentRegion.Rows(1)
    
    ' Run AdvancedFilter
    rgData.AdvancedFilter xlFilterCopy, rgCriteriaRange, rgCopyToRange  

End Sub

 

You can run this code for pretty much any AdvancedFilter that you want to do. All you need to do is to change the ranges as appropriate. You can change Sheet1 in the code to any worksheet variable or code name.

Important Note: When we run AdvancedFilter using VBA, the ranges do not need to be on the same worksheet or even in the same workbook.

 

VBA Advanced Filter Clear

If we Filter the data in place then we can use ShowAllData to remove the filter. We should check the filter is turned on first so we don’t get an error. We can use the following code to check and clear the filter if it exists:

If Sheet1.FilterMode = True Then
    Sheet1.ShowAllData
End If

 

If we are we filter using copy then Advanced Filter will automatically remove existing data from the destination before copying. However, if you want to simply clear the data you can do it like this:

 

    Sheet1.Range("E7").CurrentRegion.Offset(1).ClearContents

This highlights all the adjacent data to the output headers. It moves down one row using Offset to avoid clearing the header row.

 

 

VBA Advanced Filter Criteria

Check out this YouTube video to see me using Advanced Filter Criteria:

 

To use Criteria on a filter we use the columns headers of the List range with the criteria below them.  The following criteria will return all rows where the Fruit column contains the text Orange:

 

This criteria will return the following rows:

 

Advanced Filter Multiple Criteria

We can use the columns in any row to filter by multiple criteria. This allows us to filter using AND logic e.g. If Fruit equals “Apple” AND City equals “New York”:

 

We can use multiple rows if we want to filter using OR logic e.g. If Fruit equals “Apple” OR Fruit equals “Pear” OR Fruit equals “Plum”:

 

Let’s have a look at examples of using Multiple Criteria with Advanced Filter:

 

Advanced Filter Multiple Criteria Examples

In our first example we will start with a simple AND filter:

 

These criteria return all the rows that have the fruit Orange AND the city Berlin:

 

 

In our next example, we are looking for a city that starts with S AND has sales of less than 500:

 

These criteria will return the following rows:

 

We can use any column header multiple times in the criteria range. For example, we can use the Sales column twice to get a number between 300 and 500:

 

These criteria will return the following rows:

 

Advanced Filter Criteria – OR

We use columns in a row when we want to do an AND operation. If we want want to do an OR operation we use rows in the Criteria filter.

In the following example we want to return rows where the Fruit is either a Peach OR a Banana:

 

This will return the following rows:

 

In the following example, we want to return any rows that have a fruit Banana OR sales that are greater than 900:

 

These criteria will return the following rows:

 

Combining AND Criteria and OR Criteria

Let’s look at an example of combining AND criteria with OR criteria.

This criteria filters by rows where (Fruit is Lemon AND City is Singapore) OR (Fruit is Orange AND City is Paris):

 

These are the results:

 

Using Formulas as Criteria

While the standard criteria methods offer powerful filtering methods they have limitations. The beauty of the AdvancedFilter is that we can use worksheet formulas in the criteria.

There are 3 rules when using formulas in the criteria range:

1. No heading.
2. The formula must result in True or False.
3. The formula should reference the first row of the data range.

Imagine we have the following data:

 

We want to filter by games where the total number of goals scored was 2. We cannot do this using the normal criteria so we create a formula:

=B5+D5=2

We place this formula in cell A2:

 

You can see that we have followed the rules above:

1. We have no header in the criteria.
2. The result of the formula is False.
3. The formula refers to the first row of the data i.e. B5 and D5.

The rows we get back are:

 

Note: If we want to use a formula on another row in the Criteria we should still refer to the first row of the data e.g.:

Cell A2 formula: =B5+D5=2

Cell A3 formula: =B5+D5=5

Cell A3 formula: =B5+D5=7

 

Advanced Filter Case Sensitive Criteria

There isn’t a simple way to use case sensitivity in our Criteria therefore we use a formula instead:

=EXACT(A5, “Pea”)

Make sure to follow the Formula rules in the previous section to ensure this works correctly.

VBA Advanced Filter Dates

Take a look at the following formula for the date criteria:

=”<1/9/2021″

This formula will work fine when we run Advanced Filter from the ribbon. But if we run Advanced Filter using VBA it will not return any records.

Instead, we have to use a formula like this:

=”<” & DATE(2021,9,1)

 

When we use this in the criteria like this:

we get:

If you want to do between dates then you can use the formulas in two columns. For example, imagine we want to get all the records in August 2021, then we can use the following formulas:

A2: =”<=” & DATE(2021,8,31)

B2: =”>=” & DATE(2021,8,1)

 

This will return the following rows:

 

VBA Advanced Filter Advantages and Limitations

Advanced Filter is easy to use and does its job very well. However, like every tool it has advantages and limitations to what it can do.

The following are the advantages of using the Advanced Filter:

1. Speed – It is the fastest VBA method for copying and filtering data although multiple calls will slow it down.
2. Advanced filtering – provides in-depth filtering options including the use of formulas.
3. Requires very little code – You can use the same code most of the time and it’s simplistic compared to other methods of copying and filtering code in VBA.
4. Formatting – When copying the results it automatically formats the result data to match the original data.

 

The following are the limitations of the Advanced Filter which you should be aware of:

1. Speed – using AdvancedFilter is extremely fast but calling it multiple times in the same code, will cause it to run slower.
2. Criteria can only use ranges – you cannot use an array for the Criteria. The workaround is to write the array to a range and then use it as the Criteria.
3. Cannot alter data – AdvancedFilter simply filters and copies the data. You cannot make changes to the data after filtering and before copying.
4. Cannot append data – To append data you need to write extra code.

 

Error 1004 – Extract Range

The most common error with the advanced filter is: Error 1004 – the extract range has a missing or invalid field name.

VBA Runtime Error 1004 occurs when one or more of the output column headers do not exist in the original data as we can see in this example:

If this error occurs you should ensure that the CopyTo range has the correct column headers with the correct spelling and that it is referencing the correct range.

 

What to do if Advanced Filter is not working

In general, when Advanced Filter throws an error, the problem is in one of the ranges.

If you have an error the following checks should fix most if not all errors:

1. Ensure all the range variables are referencing the expected ranges.
2. Ensure the Criteria and CopyTo column headers are correctly spelled and exist in the column headers of the List range.
3. Ensure that the CopyTo range references the header row only.
4. Ensure that any Criteria columns using formulas do not have a header.
5. Make sure that there are no trailing spaces in Criteria.

TIP: If you are debugging the code you can check any range using the Address property of the range:

 

Wrapping Up

In this post, we’ve seen how to configure the ranges in a worksheet to be able to use Advanced Filter. We’ve also seen how, with a few lines of VBA code, we can take advantage of the power and speed of this function to filter and copy data.

We’ve also seen how to use the criteria to apply simple and complex filters and how to proceed when an error prevents us from using Advanced Filter.

Leave your comments if you want to know more about this powerful tool Excel gives us. Or if you’ve run into problems that prevent you from unleashing all its power.

What’s Next?

Free VBA Tutorial If you are new to VBA or you want to sharpen your existing VBA skills then why not try out this Free VBA Tutorial.

Related Training: Get full access to the Excel VBA training webinars and all the tutorials.

(NOTE: Planning to build or manage a VBA Application? Learn how to build 10 Excel VBA applications from scratch.)