In this article, I’ll show you how to filter multiple criteria from a dataset with VBA in Excel. You’ll learn to filter both AND type and OR type multiple criteria.
Filter Multiple Criteria in Excel with VBA: 2 Easy Methods
Here we’ve got a data set with the names, book types, and prices of a bookshop called Martin Bookstore.
Our objective today is to filter multiple criteria from this data set with Visual Basic of Applications (VBA).
Read More: How to Filter Based on Cell Value Using Excel VBA
1. Filter Multiple Criteria of AND Type in Excel with VBA
First, we’ll develop a Macro to filter multiple criteria of AND type for any data set.
For example, let’s try to filter out the books that are novels and have a price greater than $25.00.
⧭ VBA Code:
Sub Filter_Multiple_Criteria_AND_Type()
Count = 1
For i = 1 To Selection.Rows.Count
If Selection.Cells(i, 2) = "Novel" And Selection.Cells(i, 3) >= 25 Then
For j = 1 To Selection.Columns.Count
Range("F4").Cells(Count, j) = Selection.Cells(i, j)
Next j
Count = Count + 1
End If
Next i
End Sub
⧭ Output:
Select the data set from the worksheet and run this Macro (Filter_Multiple_Criteria_AND_Type).
It’ll filter the books that are novels and have prices greater than $25.00 in a new range starting from cell F4.
⧭ Notes:
- In the 4th line of the code, we’ve used Cells(i, 2) = “Novel” and Selection.Cells(i, 2) >=25.
- Here we wanted the value from the 2nd column (Book Type) to be equal to “Novel” and that from the 3rd column (Price) to be greater than or equal to $25.00.
- You change these according to your needs.
- For example, if you want books that have prices greater than or equal to $20.00 but less than or equal to $30.00, use Selection.Cells(i, 3) >=20 and Selection.Cells(i, 3) <=30.
- Also in line 6, we’ve used Range(“F4”) because we wanted the filtered data to be started from cell F4.
- You change it accordingly.
Read More: Excel VBA: How to Filter with Multiple Criteria in Array
2. Filter Multiple Criteria of OR Type in Excel with VBA
Next, we’ll develop a Macro to filter multiple criteria of OR type for any data set.
For example, let’s try to filter out the books that are novels or have a price greater than $25.00 this time.
⧭ VBA Code:
Sub Filter_Multiple_Criteria_Or_Type()
Count = 1
For i = 1 To Selection.Rows.Count
If Selection.Cells(i, 2) = "Novel" Or Selection.Cells(i, 3) >= 25 Then
For j = 1 To Selection.Columns.Count
Range("F4").Cells(Count, j) = Selection.Cells(i, j)
Next j
Count = Count + 1
End If
Next i
End Sub
⧭ Output:
Select the data set from the worksheet and run this Macro (Filter_Multiple_Criteria_OR_Type).
It’ll filter the books that are novels or have prices greater than $25.00 in a new range starting from cell F4.
⧭ Notes:
- In the 4th line of the code, we’ve used Selection.Cells(i, 2) = “Novel” or Selection.Cells(i, 2) >=25.
- Here we wanted the value from the 2nd column (Book Type) to be equal to “Novel” or that from the 3rd column (Price) to be greater than or equal to $25.00.
- You change these according to your needs.
- Also in line 6, we’ve used Range(“F4”) because we wanted the filtered data to be started from cell F4.
Read More: How to Remove Filter in Excel VBA
Things to Remember
Here we’ve used two criteria for multiple criteria. You can use as many criteria as you like. Just join all the criteria with And or Or according to the condition.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
Conclusion
Using these methods, you can filter multiple criteria in Excel with VBA, of both AND and OR types. Do you have any questions? Feel free to ask us.
