How to Use the Advanced Filter in VBA (A Step-by-Step Guideline)

Advanced Filter is one of the most important and widely used features that we use while working in Excel, as well as in Visual Basic Applications (VBA). Today in this article, I’ll show you how you can use the Advanced Filter of Excel in VBA.

Excel Advanced Filter Overview

Advanced Filter is a tool available in the tab Data under the group Sort & Filter in the Excel toolbar.

Data > Sort & Filter.

It takes a range as the input, performs a filtering operation over it, and then returns the filtered range in the original location, or in a new location (Decided by the user).

Moreover, it can be also used to filter and keep the unique values only.

Therefore, the Advanced Filter takes a total of 5 parameters.

List Range: The Range that will be Filtered.
Action: Whether to Keep the Filtered Range in the Original Location, or Copy to a New Location.
Criteria Range: The Criteria to be applied on the List Range.
Copy to: The New Location of the Filtered Range (Only in Case we Want to Copy it to a New Location).
Unique: Whether to Keep the Unique Values Only or Not.

Parameter	Explanation
List Range	The Range that will be Filtered.
Action	Decides whether to Keep the Filtered Range in the Original Location, or Copy to a New Location.
Criteria Range	The Criteria to be Applied on the List Range.
Copy to	The New Location of the Filtered Range (Only in Case we Want to Copy it to a New Location).
Unique	Decides whether to Keep the Unique Values Only or Not.

⧭ Parameter 1: List Range

This is the range that will be filtered. Enter it in the box called List range.

Or you can select the range first and then open the Advanced Filter. It’ll be added automatically to the List Range box.

Here I’ve entered the range B3:E13.

⧭ Parameter 2: Whether to Keep in the Original Location or Copy to a New Location

You can insert this parameter by checking any of the two checkboxes under the section Action.

To keep the filtered range in the original location, select Filter the list, in-place.

Or to copy the filtered range in a new location, check Copy to another location.

⧭ Parameter 3: Criteria Range

This is the range of the criteria that you want to apply. It must contain at least one header from your List range and a few values under that header.

Let’s try to filter out the novels of Charles Dickens and the poetries of P. B. Shelly. Then our criteria range will be something like this.

Enter it into the Criteria range box.

⧭ Parameter 4: Copy To

This is the range where you want to copy the filtered range. This is a row containing the headers of the columns that we want to copy.

We want to copy the names, authors, and book types of the novels of Charles Dickens and the poetries of P. B. Shelly.

So our Copy To range will be like this:

Note: The Copy To box will be active only if you put a check on the Copy to another location checkbox, otherwise it’ll remain inactive.

⧭ Parameter 5: Unique Records

Finally, put a check on the checkbox Unique records only, if you want to keep only the unique records of the filtered range.

Similar Readings

VBA Advanced Filter Overview

Hope you have understood how the Advanced Filter works in Excel. Now we’ll implement the whole operation with VBA.

The VBA command for using the Advanced Filter is:

List range.AdvancedFilter Action, Criteria Range, [Copy to], [Unique]

Let’s see the step-by-step procedure to implement the command.

Step 1: Setting the List Range of the Advanced Filter in VBA

First, you have to set the List range of the Advanced Filter in VBA. Here it’s B4:E13.

Set List_Range=Range("B4:E13")

Step 2: Fixing the Action Parameter of the Advanced Filter in VBA

Next, you have to set the Action parameter.

You can choose two options:

To keep the filtered range in the original location, choose xlFilterInPlace.
Or to copy the filtered range in a new location, choose xlFilterCopy.

Here, we, want to copy the range to a new location, so we have chosen xlFilterCopy.

Action = xlFilterCopy

Step 3: Inserting the Criteria Range Parameter of the Advanced Filter in VBA

Then you have to insert the Criteria Range parameter. In this example, it’s C16:H18.

Set Criteria_Range = Range("C16:D18")

Step 4: Entering the Copy to Range Parameter of the Advanced Filter in VBA

Then we have to enter the Copy to Range parameter, as we set the Action to xlFilterCopy.

If you set the Action to xlFilterInPlace, then you don’t need this step.

Here, we want to copy to the range H3:J3.

Set Copy_To_Range = Range("H3:J3")

Step 5: Setting the Unique Parameter of the Advanced Filter in VBA

Finally, if have to set the Unique parameter to either True or False.

To keep only the unique records, set True.

Otherwise, set False.

Here, we’ve set it to False. It’s optional. Default is False.

Unique = False

Full Code

Finally, add the line:

List_Range.AdvancedFilter Action, Criteria_Range, Copy_To_Range, Unique

So the full VBA code to use the Advanced Filter is:

Sub Advanced_Filter()

Set List_Range = Range("B3:E13")

Action = xlFilterCopy

Set Criteria_Range = Range("C16:D18")

Set Copy_To_Range = Range("H3:J3")

Unique = False

List_Range.AdvancedFilter Action, Criteria_Range, Copy_To_Range, Unique

End Sub

The Final Output

Finally, save the file as Excel Macro-Enabled Workbook.

Then run the code. You’ll find the book names, authors, and book types of the novels of Charles Dickens and the poetry of P. B. Shelly copied to your destined location.

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.

Advanced Filter.xlsm

Conclusion

So, using this method, you can use the Advanced Filter in VBA in Excel. Do you have any questions? Feel free to ask us.

Get FREE Advanced Excel Exercises with Solutions!