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.

1. List Range: The Range that will be Filtered.
2. Action: Whether to Keep the Filtered Range in the Original Location, or Copy to a New Location.
3. Criteria Range: The Criteria to be applied on the List Range.
4. Copy to: The New Location of the Filtered Range (Only in Case we Want to Copy it to a New Location).
5. Unique: 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.

Read More: Excel Advanced Filter (5 Useful Applications)

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:

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.

Read More: Excel VBA Examples with Advanced Filter Criteria (6 Cases)

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.

Read More: VBA to Copy Data to Another Sheet with Advanced Filter in Excel

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.

Read More: Advanced Filter with Criteria Range in Excel (18 Applications)

Similar Readings

• How to Apply the Advanced Filter to Copy to Another Location in Excel
• Excel Advanced Filter: Apply “Does Not Contain” (2 Methods)
• How to Use Advanced Filter If Criteria Range Contains Text in Excel
• Excel Advanced Filter Not Working (2 Reasons & Solutions)
• How to Create Dynamic Advanced Filter in Excel (2 Applications)

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.

Read More: Apply Advanced Filter Based on Multiple Criteria in One Column in Excel

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.

Read More: How to Use Advanced Filter for Unique Records Only in Excel

Full Code

Finally, add the line:

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

Read More: Excel VBA: Advanced Filter with Multiple Criteria in a Range (5 Methods)

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.

Read More: Advanced Filter with Multiple Criteria in Excel (15 Suitable Examples)

Download Practice Workbook

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

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.

Related Articles

• How to Use Advanced Filter for Date Range in Excel (2 Easy Ways)
• Use Advanced Filter with Wildcard in Excel
• How to Remove Advanced Filter in Excel (5 Effective Ways)
• Use Advanced Filter to Exclude Blank Cells in Excel (3 Easy Tricks)
• How to Use Advanced Filter to Copy Data to Another Sheet in Excel
• Use Custom Autofilter in Excel for More Than 2 Criteria
• How to Remove Duplicate Names in Excel (6 Simple Methods)