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

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.


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


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.

Data Set to Filter Multiple Criteria in Excel with VBA

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

VBA Code to Filter Multiple Criteria in Excel of AND Type

⧭ 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.

Output to Filter Multiple Criteria in Excel with VBA

⧭ Notes:

  • In the 4th line of the code, we’ve used Cells(i, 2) = “Novel” and Selection.Cells(i, 2) >=25.

VBA Code to Filter Multiple Criteria in Excel with VBA

  • 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


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

VBA Code to Filter Multiple Criteria in Excel with VBA

⧭ Output:

Select the data set from the worksheet and run this Macro (Filter_Multiple_Criteria_OR_Type).

Running Code to Filter Multiple Criteria in Excel with VBA

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.

VBA Code to Filter Multiple Criteria in Excel with VBA

  • 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

Rifat Hassan

Rifat Hassan

Hello! Welcome to my profile. Here I will be posting articles related to Microsoft Excel. I am a passionate Electrical Engineer holding a Bachelor’s degree in Electrical and Electronic Engineering from the Bangladesh University of Engineering and Technology. Besides academic studies, I always love to keep pace with the revolution in technology that the world is rushing towards day by day. I am diligent, career-oriented, and ready to cherish knowledge throughout my life.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo