Excel VBA: Advanced Filter with Multiple Criteria in a Range

VBA Advanced Filter Syntax:

AdvancedFilter: refers to a range object. You can set the 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.

We will use the sample dataset below to illustrate how to apply advanced filter with multiple criteria in a range.

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

Let’s filter the data for the product name Cookies and Chocolate. To apply the OR criteria, you should place the value in different rows.

Step 1:

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

Step 2:

Paste the following VBA code.

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:

Save the program and press F5 to run.
You will get the filtered results as shown in the image below.

Notes. To reverse the process or remove the filter, add the code below 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

You will get the previous version of your dataset.

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

We want to filter the cookies with the price of $0.65.

Step 1:

To open VBA Macro, press Alt + F11
Paste the following VBA code in the 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.
Get the filtered results.

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

For example, we want to get the values for Cookies or Chocolates, but want to add the criteria price $0.65 for the Cookies.

Step 1:

Open the VBA Macro and paste the following code.

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:

Save and press F5 to run it.
You will get the filtered results.

Method 4 – Applying VBA Advanced Filter for Unique Values with Multiple Criteria

Add the Unique argument to True to get only the unique values and delete duplicates.

Step 1:

Open the VBA Macro by pressing Alt + F11.
Paste the following VBA code.

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:

Save the program and press F5 to run it.
You will get the filtered unique values.

Method 5 – Performing Excel VBA Advanced Filter for Conditional Case

Let’s find the Total prices which are greater than $100.

Step 1:

Press Alt + F11 to open the VBA Macro.
Select a new Module and paste the following VBA code.

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:

Save the program and press the F5 to see the results.

Notes. If you want the results in a new range or worksheet, apply the xlFilterCopy action. Use the VBA code code below 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