This is the sample dataset.
Method 1 – Filtering Different Columns by Multiple Criteria Using the VBA With Statement
To Filter TVs which cost more than $1500:
- Open the Developer Tab and select Visual Basic.
- In Microsoft Visual Basic for Application, select Insert >> Module
- Enter 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
The Macro name is defined as MultipleCriteria. Criteria are set using the VBA Range method. As TV is in column 2 (Products), the AutoFilter Field is set as 2 and the Autofilter Field is set as 3: the Expenses column. The code will return the TVs bought for a value more than or equal to $1500.
- Go back to the sheet and run the Macro (MultipleCriteria).
- This is the output.
Read More: Filter Multiple Criteria in Excel with VBA (Both AND and OR Types)
Method 2 – Using Excel VBA and the AND Operator to Filter Different Columns by Multiple Criteria
Steps:
- Enter the following code in the VBA Module. (To see how to open a VBA Module, see Method 1)
Sub MultipleCriteriaAndFilter()
Worksheets("xland_filter").Range("B4:E13").AutoFilter Field:=4, _
Criteria1:=">2", Operator:=xlAnd, Criteria2:="<=5"
End Sub
The Macro name is defined as MultipleCriteriaAndFilter. B4:E13 is defined using the Range Method. There are 2 different criteria which are set using Criteria Property of VBA.
- Go back to the sheet and run the Macro (MultipleCriteriaAndFilter).
- This is the output.
Read More: How to Filter Based on Cell Value Using Excel VBA
Method 3 – Using Excel VBA and the OR Operator to Filter Different Columns by Multiple Criteria
To Filter the data with a price of less than 1600 dollars or more than or equal to 2100 for a product:
Steps:
- Enter the following code in the VBA Module. (To see how to open a VBA Module, see Method 1)
Sub MultipleCriteriaOrFilter()
Worksheets("xlor_filter").Range("B4").AutoFilter Field:=3, _
Criteria1:="<1600", Operator:=xlOr, Criteria2:=">=2100"
End Sub
The Macro name is defined as MultipleCriteriaOrFilter. Range B4 is defined using the Range Method. There are 2 different criteria which are set using the Criteria Property of VBA. Information about Expense is set in Criteria1.
- Go back to the sheet and run the Macro (MultipleCriteriaOrFilter).
- This is the output.
Read More: Excel VBA: How to Filter with Multiple Criteria in Array
Practice Section
Practice here.
Download Practice Workbook
Related Articles
- How to Remove Filter in Excel VBA
- Excel VBA to Filter in Same Column by Multiple Criteria
- Excel VBA: Filter Based on Cell Value on Another Sheet
- Excel VBA: Filter Table Based on Cell Value
- VBA Code to Filter Data in Excel
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.