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

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.


Download Practice Workbook

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


5 Effective Methods for VBA Advanced Filter with Multiple Criteria in a 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: 

Sample Data

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

Sample Data

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

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.

Methods for VBA Advanced Filter with Multiple Criteria in a Range in Excel

Step 1:

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

Methods for VBA Advanced Filter with Multiple Criteria in a Range in Excel

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

Methods for VBA Advanced Filter with Multiple Criteria in a Range in Excel

Step 3:

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

Methods for VBA Advanced Filter with Multiple Criteria in a Range in Excel

 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

Methods for VBA Advanced Filter with Multiple Criteria in a Range in Excel

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

Methods for VBA Advanced Filter with Multiple Criteria in a Range in Excel

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


2. Perform VBA Advanced Filter for the 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.

Methods for VBA Advanced Filter with Multiple Criteria in a Range in Excel

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

Methods for VBA Advanced Filter with Multiple Criteria in a Range in Excel

Step 2:

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

Methods for VBA Advanced Filter with Multiple Criteria in a Range in Excel

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


3. Utilize VBA Advanced Filter for the OR with AND Criteria in a Range in Excel

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.

Methods for VBA Advanced Filter with Multiple Criteria in a Range in Excel

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

Methods for VBA Advanced Filter with Multiple Criteria in a Range in Excel

Step 2:

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

Methods for VBA Advanced Filter with Multiple Criteria in a Range in Excel

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


Similar Readings:


4. Use VBA Advanced Filter for Unique Values with Multiple Criteria in Excel

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.

Methods for VBA Advanced Filter with Multiple Criteria in a Range in Excel

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

Methods for VBA Advanced Filter with Multiple Criteria in a Range in Excel

Step 2:

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

Methods for VBA Advanced Filter with Multiple Criteria in a Range in Excel

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


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

Sample Data

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

Sample Data

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

Sample Data

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

Sample Data

Read More:How to Use Advanced Filter If Criteria Range Contains Text in Excel


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.

The Exceldemy staff will respond to your inquiries as quickly as possible.

Stay with us and keep learning.


Related Articles

Bhubon Costa

Bhubon Costa

Myself, Bhubon Costa, an engineer, is currently employed with Exceldemy as an Excel & VBA Content Developer. I appreciate solving analytical difficulties, finding practical answers, and, for the most part, I enjoy taking on new tasks. I write articles on Exceldemy about various ways to get out of Microsoft Excel's stuck conditions. My passion is to leave my mark on the world through my work and to have an impact on the community who benefit from it.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo