Excel VBA: Advanced Filter with Multiple Criteria in a Range

When working with big amounts of data and required to set multiple filters at once, Advanced Filtering in Excel comes in handy. It may also be applied to clean up your data by removing copies. While applying the Advanced Filter, the VBA code is much easier to execute. In this tutorial, we will show you how to apply VBA Advanced Filter for multiple criteria range in Excel.

In the following sections below, we will discuss 5 methods to use VBA Advanced Filter for multiple criteria. At first, you may need to know the syntax of the VBA Advanced Filter.

 VBA Advanced Filter Syntax: 

• AdvancedFilter: refers to a range object. You can set your range where you want to apply the Filter.
• Action: is a required argument which has two options, xlFilterInPlace or xlFilterCopy. xlFilterInPlace is used to filter the value at the place where the dataset is. xlFilterCopy is used to get the filter value in another desired location.
• CriteriaRange: represents the criteria for which the value will be filtered.
• CopyToRange: is the location where you’ll save your filter results.
• Unique: is an optional argument. Use the True argument to only filter the unique values. Otherwise, by default, it is regarded as False.

In the image below, a sample data set is provided to apply all the filters we want to perform.

1. Applying Excel VBA Advanced Filter for OR Criteria in a Range

In the first method, we will apply the OR criteria using the VBA Advanced Filter. Let’s say, we want to filter the data for the product name Cookies and Chocolate. To apply the OR criteria, you should place the value in different rows. Follow the outlined steps below to do the task.

Step 1:

• Press  Alt  +  F11  to open the VBA Macro.
• Click on the Insert.
• Choose the Module.

Step 2:

• Then, paste the following VBA code to apply the OR.

Sub Apply_VBA_Advanced_Filter_for_OR_Criteria()
'Declare Variable for dataset range and for criteria range
   Dim Dataset_Rng As Range
   Dim Criteria_Rng As Range
'Set the location and range of datase range and criteria range
   Set Dataset_Rng = Sheets("Sheet1").Range("B4:E11")
   Set Criteria_Rng = Sheets("Sheet1").Range("B14:E16")
'Apply Advanced Filter to filter the dataset using the criteria
   Dataset_Rng.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Criteria_Rng
End Sub

Step 3:

• Then, save the program and press F5 to run.
• Therefore, you will get the filtered results as shown in the image below.

 Notes.  To reverse the process or remove all the filter paste and run the VBA program.

Sub Remove_All_Filter()
   On Error Resume Next
'command to remove all the filter to show the previous dataset
   ActiveSheet.ShowAllData
End Sub

• As a result, you will get the previous version of your data set.

Read More: Excel VBA Examples with Advanced Filter Criteria

2. Performing VBA Advanced Filter for AND Criteria in a Range in Excel

Similar to the previous method, we will now perform the VBA Advanced Filter for the AND criteria. Let’s say we want to know the cookies with the price of $0.65 as shown in the screenshot below. For applying the OR criteria, you should place the value in different columns. To apply the AND criteria, follow the instructions below.

Step 1:

• To open VBA Macro, press  Alt  +  F11 
• After opening the VBA Macro, paste the following VBA codes in a new Module.

Sub Apply_VBA_Advanced_Filter_for_AND_Criteria()
'Declare Variable for dataset range and for criteria range
   Dim Dataset_Rng As Range
   Dim Criteria_Rng As Range
'Set the location and range of dataset range and criteria range
   Set Dataset_Rng = Sheets("Sheet2").Range("B4:E11")
   Set Criteria_Rng = Sheets("Sheet2").Range("B14:E15")
'Apply Advanced Filter to filter the dataset using the criteria
   Dataset_Rng.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Criteria_Rng
End Sub

Step 2:

• Press F5 to run the program after you have saved it.
• Finally, get the filtered results.

3. Using VBA Advanced Filter for OR with AND Criteria in a Range

You can also apply the OR and the AND criteria both in combination. For example, you want to get the values for Cookies or Chocolates, but for the Cookies, there is another criteria price $0.65 will be applied. Follow the procedures below to get it done.

Step 1:

• Paste the following VBA codes after opening the VBA Macro.

Sub Apply_VBA_Advanced_Filter_for_OR_with_AND_Criteria()
'Declare Variable for dataset range and for criteria range
   Dim Dataset_Rng As Range
   Dim Criteria_Rng As Range
'Set the location and range of dataset range and criteria range
   Set Dataset_Rng = Sheets("Sheet3").Range("B4:E11")
   Set Criteria_Rng = Sheets("Sheet3").Range("B14:E16")
'Apply Advanced Filter to filter the dataset using the criteria
   Dataset_Rng.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Criteria_Rng
End Sub

Step 2:

• Then, save the program first at press F5 to run it.
• Consequently, you will find the values with certain AND and OR.

4. Applying VBA Advanced Filter for Unique Values with Multiple Criteria

Moreover, if you have duplicates in your data set, you can remove them while filtering. We will add the Unique argument to True to get only the unique values and delete the duplicates. Follow the outlined instructions.

Step 1:

• Firstly, open the VBA Macro by pressing  Alt  + F11.
• Paste the following VBA codes in a new Module.

Sub Apply_VBA_Advanced_Filter_for_Unique_Values()
'Declare Variable for dataset range and for criteria range
   Dim Dataset_Rng As Range
   Dim Criteria_Rng As Range
'Set the location and range of dataset range and criteria range
   Set Dataset_Rng = Sheets("Sheet4").Range("B4:E11")
   Set Criteria_Rng = Sheets("Sheet4").Range("B14:E16")
'Apply Advanced Filter to filter the dataset using the criteria
   Dataset_Rng.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Criteria_Rng, Unique:=True
End Sub

Step 2:

• Then, press F5  to run the program after saving.
• Therefore, you will obtain values only for the unique.

5. Performing Excel VBA Advanced Filter for Conditional Case

In addition to the previous methods, you can also apply conditions with formulas. For example, we want to find the Total prices which are greater than $100. To accomplish it, simply follow the steps.

Step 1:

• Firstly, to open the VBA Macro, press  Alt + F11.
• Select a new Module and paste the following VBA codes.

Sub Apply_VBA_Advanced_Filter_for_Formula()
'Declare Variable for dataset range and for criteria range
   Dim Dataset_Rng As Range
   Dim Criteria_Rng As Range
'Set the location and range of dataset range and criteria range
   Set Dataset_Rng = Sheets("Sheet5").Range("B4:E11")
   Set Criteria_Rng = Sheets("Sheet5").Range("B14:E15")
'Apply Advanced Filter to filter the dataset using the criteria
   Dataset_Rng.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Criteria_Rng
End Sub

Step 2:

• Secondly, save the program and press the F5 button to see the results.

 Notes.  Additionally, you can get the results in a favorable space whether in a new range or in a new worksheet by applying the xlFilterCopy action. Simply, paste the VBA codes and run them to get the results in Sheet6 in range B4:E11.

'Declare Variable for dataset range and for criteria range
   Dim Dataset_Rng As Range
   Dim Criteria_Rng As Range
'Set the location and range of dataset range and criteria range
   Set Dataset_Rng = Sheets("Sheet5").Range("B4:E11")
   Set Criteria_Rng = Sheets("Sheet5").Range("B14:E15")
'Apply Advanced Filter to filter the dataset using the criteria
Dataset_Rng.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Criteria_Rng, CopyToRange:=Sheets("Sheet6").Range("B4:E11")
End Sub

• Consequently, see the final result in a new worksheet ‘Sheet6’.

Download Practice Workbook

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

Conclusion

To summarize, I hope you now understand how to use the VBA advanced filter in Excel to filter multiple criteria ranges. All of these methods should be used to teach and practice with your data. Look over the practice book and put what you’ve learned to use. Because of your vital support, we are inspired to continue presenting seminars like this.

If you have any questions, please do not hesitate to contact us. Please let us know what you think in the comments section below.

Stay with us and keep learning.

Related Article

• VBA to Copy Data to Another Sheet with Advanced Filter in Excel