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.
|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.
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.
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")
- 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.
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
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.
So, using this method, you can use the Advanced Filter in VBA in Excel. Do you have any questions? Feel free to ask us.
- 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)