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 Application (VBA). Today in this article, I’ll show you how you can use the Advanced Filter of Excel in VBA.


Advanced Filter in VBA (Quick View)

VBA Code to Use the Advanced Filter


Download Practice Workbook

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


Introduction to the Excel Advanced Filter

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

Data > Sort & Filter.

Advanced Filter in Excel Toolbar

 

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 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.

Advanced Filter in Excel

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.

Entering List Range in Advanced Filter

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.

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.

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.

Entering Criteria Range in Advanced Filter

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.

Selecting Unique Records in Advanced Filter in Excel


How to Use the Advanced Filter in VBA

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: Set 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: Fix 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

VBA Code to Use Advanced Filter in Excel


Step 3: Insert 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")

VBA Code to Use Advanced Filter in Excel


Step 4: Enter 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: Set 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

VBA Code to Use Advanced Filter in Excel


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

VBA Code to Use the Advanced Filter


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.


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.

Rifat Hassan

Hello! Welcome to my profile. Here I will be posting articles related to Microsoft Excel. I am a passionate Electrical Engineer holding a Bachelor’s degree in Electrical and Electronic Engineering from Bangladesh University of Engineering and Technology. Besides academic studies, I always love to keep pace with the revolution in technology that the world is rushing towards day by day. I am diligent, career-oriented, and ready to cherish knowledge throughout my life.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo