Filter Different Column by Multiple Criteria in Excel VBA (3 Ways)

Get FREE Advanced Excel Exercises with Solutions!

The article will provide some different methods on how to Filter multiple criteria in different column(s) in Excel VBA. Here we have a dataset where we store the names of some businessmen, which products they bought for selling purposes, the quantity of the products, and how much money they used.

excel vba filter multiple criteria different column


Filter Different Column by Multiple Criteria in Excel VBA: 3 Ways

1. Filtering Different Columns by Multiple Criteria Using VBA With Statement

Suppose you want to Filter those TVs which will cost these businessmen more than 1500 bucks. We can do this by using the VBA With statement. Let’s discuss the process below.

Steps:

  • First, open the Developer Tab and select Visual Basic.

  • Then it will open a new window of Microsoft Visual Basic for Application. Select Insert >> Module

excel vba filter multiple criteria different column

  • Type the following code in the VBA Module.
Sub MultipleCriteria()
With Range("B4:E4")
.AutoFilter Field:=2, Criteria1:="TV"
.AutoFilter Field:=3, Criteria1:=">=1500"
End With
End Sub

Here, we define the name of Macro as MultipleCriteria. We set the criteria by using the VBA Range method. As TV is in column number 2 (Products), we set the AutoFilter Field as 2 and for the same reason, we set the Autofilter Field as 3 which refers to the Expenses column. The execution of this code will show us the TVs bought by these businessmen at an expenditure of more than or equal to 1500 bucks.

  • Now, go back to the sheet and run the Macro which is named MultipleCriteria.

excel vba filter multiple criteria different column

  • After that, you will see the information about TV and the cost of more than 1500 dollars for these guys.

excel vba filter multiple criteria different column

Thus you can Filter multiple criteria from different columns using VBA With statement.

Read More: Filter Multiple Criteria in Excel with VBA (Both AND and OR Types)


2. Using Excel VBA AND Operator to Filter Different Columns by Multiple Criteria

Using the VBA AND operator is a quick way to Filter multiple criteria in different columns. Suppose we want to see the products which the buyers bought between 3 to 5 times. Let’s discuss the procedure below.

Steps:

  • Type the following code in the VBA Module. (To see how to open a VBA Module, please go to Section 1)
Sub MultipleCriteriaAndFilter()
Worksheets("xland_filter").Range("B4:E13").AutoFilter Field:=4, _
Criteria1:=">2", Operator:=xlAnd, Criteria2:="<=5"
End Sub

Here, we define the name of our Macro as MultipleCriteriaAndFilter. We define the range B4:E13 via the Range Method. We have 2 different criteria so we set them by the Criteria Property of VBA. 

  • Now, go back to the sheet and run the Macro which is named MultipleCriteriaAndFilter.

excel vba filter multiple criteria different column

  • After that, you will see the information about TV and the money that these guys spent to buy them.

Thus you can easily Filter multiple criteria in different columns by using the VBA And operator.

Read More: How to Filter Based on Cell Value Using Excel VBA


3. Using Excel VBA OR Operator to Filter Different Columns by Multiple Criteria

Using the VBA OR operator is a quick way to Filter multiple criteria in different columns. Suppose we want to Filter the data where we see people paid the price of less than 1600 dollars or more than or equal to 2100 for a product. Let’s discuss the procedure below.

Steps:

  • Type the following code in the VBA Module. (To see how to open a VBA Module, please go to Section 1)
Sub MultipleCriteriaOrFilter()
Worksheets("xlor_filter").Range("B4").AutoFilter Field:=3, _
Criteria1:="<1600", Operator:=xlOr, Criteria2:=">=2100"
End Sub

Here, we define the name of our Macro as MultipleCriteriaOrFilter. We define the range B4 via the Range Method. There are 2 different criteria so we set them by the Criteria Property of VBA. We want to see the information about Expense so we set it in Criteria1.

  • Now, go back to the sheet and run the Macro which is named MultipleCriteriaOrFilter.

excel vba filter multiple criteria different column

  • After that, you will see the price range we defined in the VBA code.

Thus you can easily Filter multiple criteria in different columns by using the VBA Or operator.

Read More: Excel VBA: How to Filter with Multiple Criteria in Array


Practice Section

Here I’m giving you the dataset that we worked on for this article so that you can practice these methods on your own.

excel vba filter multiple criteria different column


Download Practice Workbook


Conclusion

In a nutshell, you will learn some easy methods on how to Filter multiple criteria in different columns by Excel VBA after reading this article. If you have any better methods ideas or feedback, please leave them in the comment box. Your valuable tips will help me enrich my upcoming articles.


Related Articles

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Meraz Al Nahian
Meraz Al Nahian

Md. Meraz Al Nahian has worked with the ExcelDemy project for over 1.5 years. He wrote 140+ articles for ExcelDemy. He also solved a lot of user problems and worked on dashboards. He is interested in data analysis, advanced Excel, statistics, and dashboards. He also likes to explore various Excel and VBA applications. He completed his graduation in Electrical & Electronic Engineering from Bangladesh University of Engineering & Technology (BUET). He enjoys exploring Excel-related features to gain efficiency... Read Full Bio

2 Comments
  1. Thanks for the tutorial, but your “or” example doesn’t work. Your example is that you want to return any order where a customer purchased a television OR the customer spent less than or equal to $2000 on any item. Instead, your code returns all TV purchases regardless of amount. You are applying the Expense filter to Field 2 instead of Field 3. Since there are no rows where Product = $2000 or less, that filter is ignored and all TVs are returned. What you want is code where the result is Adam, Alan, Bryan, Jon, Lambert, James (all of whom purchased items for $2k or less) AND Shawn who bought a TV at any price.

    • Hello Mr. Gurry, thank you so much for the feedback. I have updated the article according to your suggestions. What I did before is that I tried to use the xlOr operator for two different AutoFilter Field. But this can be done by With Statement and I showed the process in Method 1. Here I changed the conditions too. Please check again if there’s anything you want to add.

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo