Filter Different Column by Multiple Criteria in Excel VBA

The article will provide some different methods on how to Filter multiple criteria in a 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


Download Practice Workbook


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

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 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 the 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: How to Filter Column Based on Another Column in Excel (5 Methods)


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

Using 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 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 VBA And operator.

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


Similar Readings


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

Using 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 buy a TV or they paid less than or equal to 2000 dollars for it. 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:=2, _
Criteria1:="TV", Operator:=xlOr, Criteria2:="<=2000"
End Sub

excel vba filter multiple criteria different column

Here, we define the name of our Macro as MultipleCriteriaOrFilter. We define the range B4 via Range Method. There are 2 different criteria so we set them by the Criteria Property of VBA. We want to see the information about TV 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 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 VBA Or operator.

Read More: Filter Multiple Criteria in Excel (4 Suitable Ways)


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


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 or ideas or feedback, please leave them in the comment box. Your valuable tips will help me enrich my upcoming articles.


Related Articles

Nahian

Nahian

Hello, Nahian here! I do enjoy my efforts to help you understand some little basics on Microsoft Excel I've completed my graduation in Electrical & Electronic Engineering from BUET and I want to be a successful engineer in my life through intellect and hard-work, and that is the goal of my career.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo