In this article, I’ll show you how you can filter multiple criteria from a dataset with VBA in Excel. You’ll learn to filter both AND type and OR type multiple criteria.
2 Easy Methods to Filter Multiple Criteria in Excel with VBA
Here we’ve got a data set with the names, book types, and prices of a book shop called Martin Bookstore.
Our objective today is to filter multiple criteria from this data set with Visual Basic of Applications (VBA).
1. Filter Multiple Criteria of AND Type in Excel with VBA
First of all, 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 the 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: Filter Multiple Criteria in Excel
Similar Readings
- How to Perform Custom Filter in Excel (5 Ways)
- Add Filter in Excel (4 Methods)
- Shortcut for Excel Filter (3 Quick Uses with Examples)
- How to Use Text Filter in Excel (5 Examples)
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 Apply Multiple Filters in Excel [Methods + 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.
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. And don’t forget to visit our site ExcelDemy for more posts and updates.
Further Readings
- How to Filter Multiple Columns in Excel Independently
- Search Multiple Items in Excel Filter (2 Ways)
- How to Remove Filter in Excel (5 Easy & Quick Ways)
- Filter by Date in Excel (4 Quick Methods)