Method 1 – Filter Multiple Criteria of AND Type in Excel with VBA
Develop a Macro to filter multiple criteria of AND type for any data set.
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 used Cells(i, 2) = “Novel” and Selection.Cells(i, 2) >=25.
- 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.
- Change these according to your needs.
- If you want books with 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.
- In line 6, we used Range(“F4”) because we wanted the filtered data to be started from cell F4.
- Change it accordingly.
Method 2 – Filter Multiple Criteria of OR Type in Excel with VBA
Develop a Macro to filter multiple criteria of OR type for any data set.
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).
Start from cell F4 and filter the novels or books with prices greater than $25.00 in a new range.
ā§ Notes:
- In the 4th line of the code, we used Selection.Cells(i, 2) = “Novel” or Selection.Cells(i, 2) >=25.
- 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.
- Change these according to your needs.
- In line 6, we used Range(“F4”) because we wanted the filtered data to be started from cell F4.
Things to Remember
You can use as many criteria as you like. 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.
Further Readings
- 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
- Filter Different Column by Multiple Criteria in Excel VBA
- Excel VBA to Filter in Same Column by Multiple Criteria
