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

In this article, we’ll demonstrate how to use Excel’s Advanced Filter in VBA.

VBA Code to Use the Advanced Filter

Excel Advanced Filter Overview

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

Advanced Filter in Excel Toolbar

It takes a range as input, performs a filtering operation over it, and returns the filtered range in the original location, or in a new location (at the user’s discretion). It can be also used to filter and keep unique values only.

The Advanced Filter takes 5 parameters:

List Range: The range that will be filtered.
Action: Keep the filtered range in the same location, or move it.
Criteria Range: The criteria to be applied to the List Range.
Copy to: The new location of the filtered range (if required).
Unique: Keep unique values only, or not.

Advanced Filter in Excel

Parameter 1 – List Range

Enter it in the box called List range, or select the range first and then open the Advanced Filter. It’ll be added automatically to the List Range box. Here we use B3:E13.

Entering List Range in Advanced Filter

Parameter 2 – Original Location or Copy to a New Location

Select one of the two checkboxes under the section Action.
To keep the filtered range in the original location, select Filter the list, in-place.

Checking Checkbox in Advanced Filter in Excel

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.

If we try to filter out the novels of Charles Dickens and the poetry books of P. B. Shelly, our criteria range will be something like the image below.

Enter it into the Criteria range box.

Entering Criteria Range in Advanced Filter

Parameter 4 – Copy To

This is the location where the filtered range will be copied, 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 poetry books 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

Check Unique records only if you want to keep only the unique records of the filtered range.

Selecting Unique Records in Advanced Filter in Excel

Similar Reading

VBA Advanced Filter Overview

We can also apply the Advanced Filter with VBA. The VBA command for using the Advanced Filter is:

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

Step 1 – Setting the List Range

First, set the List range. Here it’s B4:E13.

Set List_Range=Range("B4:E13")

Step 2 – Setting the Action Parameter

There are two options:

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

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

Action = xlFilterCopy

VBA Code to Use Advanced Filter in Excel

Step 3 – Inserting the Criteria Range Parameter

Here, the Criteria Range is C16:H18.

Set Criteria_Range = Range("C16:D18")

VBA Code to Use Advanced Filter in Excel

Step 4 – Entering the Copy to Range Parameter

Since we set the Action to xlFilterCopy, we have to enter the Copy to Range parameter,

If you set the Action to xlFilterInPlace, skip this step.

Here, the Copy To Range is H3:J3.

Set Copy_To_Range = Range("H3:J3")

Step 5 – Setting the Unique Parameter

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, which is the default.

Unique = False

VBA Code to Use Advanced Filter in Excel

Full Code

Finally, apply the Advanced Filter using the parameters.

List_Range.AdvancedFilter Action, Criteria_Range, Copy_To_Range, Unique

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

VBA Code to Use the Advanced Filter

The Final Output

Save the file as Excel Macro-Enabled Workbook.

Run the code.

The book names, authors, and book types of the novels of Charles Dickens and the poetry of P. B. Shelly are copied to the specified location.