How to Filter with Multiple Criteria in Excel VBA

Filtering data in Excel is easy when you do it by hand, but it can take a lot of time if the table is big or the filters are complicated. With VBA, you can apply many filter conditions at once, hide rows that don’t match, and even copy the results to another sheet, all with one click and no manual work..

In this article, we’ll explore four practical ways to apply an Excel VBA filter with multiple criteria. You’ll learn how to use AutoFilter for several values in one column, combine AutoFilter across different columns, build AdvancedFilter rules for AND and OR logic, and create range-based filters for dates or numbers.

Sub AdvancedFilter_AND_OR()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim dataRange As Range, criteriaRange As Range
    Dim rngVisible As Range
    Set ws = Worksheets("Sheet4")  ' Change to your sheet name
    ' Define the data range (columns A to E)
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    Set dataRange = ws.Range("A1:E" & lastRow)
    ' Clear any existing filters
    If ws.FilterMode Then ws.ShowAllData
    ' ----- AND logic (criteria side-by-side on same row) -----
    With ws
        .Range("G1").Value = .Range("A1").Value  ' Region header
        .Range("H1").Value = .Range("E1").Value  ' Status header
        .Range("G2").Value = "East"               ' Region criteria
        .Range("H2").Value = "Pending"            ' Status criteria
        Set criteriaRange = .Range("G1:H2")
    End With
    dataRange.AdvancedFilter _
        Action:=xlFilterInPlace, _
        CriteriaRange:=criteriaRange, _
        Unique:=False
    ' ----- Uncomment below to test OR logic (criteria stacked in rows) -----
'    With ws
'        .Range("G1").Value = .Range("A1").Value  ' Region header
'        .Range("H1").Value = .Range("E1").Value  ' Status header
'
'        .Range("G2").Value = "East"               ' First OR criteria: Region=East
'        .Range("H2").ClearContents
'
'        .Range("G3").ClearContents
'        .Range("H3").Value = "Pending"            ' Second OR criteria: Status=Pending
'
'        Set criteriaRange = .Range("G1:H3")
'    End With
'
'    dataRange.AdvancedFilter _
'        Action:=xlFilterInPlace, _
'        CriteriaRange:=criteriaRange, _
'        Unique:=False
    ' Count visible rows after filter
    On Error Resume Next
    Set rngVisible = dataRange.Columns(1).SpecialCells(xlCellTypeVisible)
    On Error GoTo 0
    If rngVisible Is Nothing Then
        MsgBox "No rows meet the criteria."
    Else
        MsgBox rngVisible.Count - 1 & " row(s) remain visible."
    End If
End Sub

Filtering data in Excel manually is fine for small sets, but when you have many rows and want to filter by several values in the same column, VBA AutoFilter helps automate this. You can filter a single column by multiple criteria simultaneously, such as filtering for Region “North” or “West” at once.

For this demo, we’ll use the dataset on a sheet named Data with columns Region, Product, Quantity, OrderDate, and Status. The macro applies AutoFilter to the Region column using two criteria at the same time.

The filtered table will show only rows where the Region is either “North” or “West.” This method is fast and straightforward when your filtering is limited to multiple values within one column.

Steps:

➤ Create a copy of your original dataset to use this method. You will apply the method to the copied dataset.
➤ Press  Alt  +  F11  to open the VBA Editor.
➤ Insert a new module: go to Insert >> Module.

➤ Copy and paste the following code:

Sub FilterMultipleCriteriaOneColumn()
    Dim ws As Worksheet
    Set ws = Worksheets("Sheet1")
    ws.AutoFilterMode = False   'clear existing filters
    'Filter the first column (Region) for "North" OR "West"
    ws.Range("A1").AutoFilter _
        Field:=1, _
        Criteria1:=Array("North", "West"), _
        Operator:=xlFilterValues
End Sub

➤ Close the VBA Editor and return to Excel.
➤ Press  Alt  +  F8  , select FilterMultipleCriteriaOneColumn, and click Run. Make sure you are applying this method to the copied dataset and not the original one.

The sheet will now show only rows where the Region is “North” or “West.”. It will hide the columns that do not match the criteria.

➤ You can customize the array with any values you want to filter by in that column.

Filtering on multiple columns with different criteria is common when you want to narrow down data precisely. With VBA AutoFilter, you can specify conditions for each column individually, such as showing only rows where Region is “North” or “West” and Status is “Pending” or “Shipped.”

The macro will filter Region to “North” or “West” while filtering Status to “Pending” or “Shipped” simultaneously. After running the macro, only rows meeting both conditions will remain visible. This method combines multiple filters to work together with AND logic between columns and OR logic within each column’s criteria.

Steps:

➤ Open the VBA Editor with  Alt  +  F11  .
➤ Insert a new module via Insert >> Module.
➤ Paste the following code:

Sub FilterMultipleCriteriaMultipleColumns()
    Dim ws As Worksheet
    Set ws = Worksheets("Data")
    ws.AutoFilterMode = False   'Clear existing filters
    With ws.Range("A1").CurrentRegion
        'Filter Region (Field 1) for North or West
        .AutoFilter Field:=1, Criteria1:=Array("North", "West"), Operator:=xlFilterValues
        'Filter Status (Field 5) for Pending or Shipped
        .AutoFilter Field:=5, Criteria1:=Array("Pending", "Shipped"), Operator:=xlFilterValues
    End With
End Sub

➤ This method is ideal when filtering on multiple columns with complex criteria per column.
➤ Close the editor, return to Excel.
➤ Run the macro via  Alt  +  F8  , select FilterMultipleCriteriaMultipleColumns, and click Run.

Only rows with Region as “North” or “West” and Status as “Pending” or “Shipped” will be shown.

Excel’s AdvancedFilter lets you filter data based on more complex conditions than AutoFilter, including logical AND and OR combinations. Unlike AutoFilter, AdvancedFilter uses a criteria range where you set up rules on one or multiple columns.

For this demonstration, we’ll use the Data sheet and create a criteria range that filters rows where the Region is “East” AND the Status is “Pending.” Then, we’ll modify it to filter rows where Region is “East” OR Status is “Pending.”

This method shows how to set up criteria ranges properly for AND vs OR logic and run the AdvancedFilter to extract matching rows to place elsewhere or filter in place.

Steps:

➤ On the Data sheet, pick two empty columns well to the right of your table (for example, G and H).
➤ In G1 type Region. In H1 type Status, these must match the table headers exactly.
➤ In G2 type East. In H2 type Pending.

➤ Open the VBA Editor with  Alt  +  F11  .
➤ Insert a new module via Insert >> Module.
➤ Add the following VBA code:

Sub AdvancedFilter_AND()      '‑‑‑‑ change to _OR if you like
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim dataRange As Range, criteriaRange As Range
    Dim rngVisible As Range
    Set ws = Worksheets("Sheet3")                    'your sheet
    '--- data block -------------------------------------------------------
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    Set dataRange = ws.Range("A1:E" & lastRow)      'columns A‑E
    If ws.FilterMode Then ws.ShowAllData            'clear any old filter
    '======================================================================
    '  *****   A N D   V E R S I O N   (active)   **************************
    '======================================================================
    '  Criteria are placed on the *same* row ⇒ all must be TRUE
    '----------------------------------------------------------------------
    With ws
        'headers – must match data block exactly
        .Range("G1").Value = .Range("A1").Value      'Region
        .Range("H1").Value = .Range("E1").Value      'Status
        'criteria values
        .Range("G2").Value = Trim$("North")
        .Range("H2").Value = Trim$("Pending")
        Set criteriaRange = .Range("G1:H2")          'one criteria row
    End With
    dataRange.AdvancedFilter _
        Action:=xlFilterInPlace, _
        CriteriaRange:=criteriaRange, _
        Unique:=False
    '----------------------------------------------------------------------
'=======================================================================
'  *****   O R   V E R S I O N   (commented‑out)   *********************
'=======================================================================
'  Each OR clause goes on a *different* row under the headers:
'      Row‑2: Region = North     (Status blank)
'      Row‑3: Status = Pending   (Region blank)
'-----------------------------------------------------------------------
'    With ws
'        'headers – must match data block exactly
'        .Range("G1").Value = .Range("A1").Value      'Region
'        .Range("H1").Value = .Range("E1").Value      'Status'
'
'        'first OR clause (Region = North)
'        .Range("G2").Value = Trim$("North")
'        .Range("H2").ClearContents
'
'        'second OR clause (Status = Pending)
'        .Range("G3").ClearContents
'        .Range("H3").Value = Trim$("Pending")
'
'        Set criteriaRange = .Range("G1:H3")         '← note 3 rows
'    End With
'
'    dataRange.AdvancedFilter _
'        Action:=xlFilterInPlace, _
'        CriteriaRange:=criteriaRange, _
'        Unique:=False
'-----------------------------------------------------------------------
    '--- how many rows are visible? ---------------------------------------
    On Error Resume Next
    Set rngVisible = dataRange.Columns(1).SpecialCells(xlCellTypeVisible)
    On Error GoTo 0
    If rngVisible Is Nothing Then
        MsgBox "No rows meet the criteria."
    Else
        MsgBox rngVisible.Count - 1 & _
               " row(s) remain visible."
    End If
End Sub

➤ On your Data sheet, set up the criteria range as explained in the comments (cells G1:H2 for AND criteria).
➤ Run the macro, and it will filter the data in place using AND logic.

Uncomment the OR section in the code and adjust the criteria range if you want to try OR logic.

The code should now filter using the OR logic

➤ This method is powerful for complex filtering needs beyond AutoFilter’s capabilities.

Filtering rows that fall within a specific period is a common task. With VBA you can use AdvancedFilter to keep only the records whose OrderDate is between two dates you choose.

For this demo, we will work with the same Data sheet that holds Region, Product, Quantity, OrderDate, and Status. Our goal is to show only rows dated 01‑Jan‑2025 to 31‑Mar‑2025. After you run the macro, the table shrinks to the matching dates. You can filter in place or copy the results elsewhere for a clean report.

Steps:

➤ Add a blank separator column F (leave the whole column empty).
➤ In G1 type OrderDate (match the header in E1 exactly).
➤ In G2 type =”>=” & DATE(2025,1,1).
➤ In G3 type =”<=” & DATE(2025,3,31).

➤ Press  Alt  +  F11  to open the VBA Editor.
➤ Go to Insert >> Module to add a new module.
➤ Paste the code below:

Sub Filter_DateRange_Sheet4_FarRight()
    Dim ws As Worksheet, rngData As Range, rngCrit As Range
    Dim lastRow As Long, c As Range
    Dim startDate As Date, endDate As Date
    Set ws = Worksheets("Sheet4")
    startDate = DateSerial(2025, 1, 1)
    endDate   = DateSerial(2025, 3, 31)
    '----- locate data block A:E -------------------------------------
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    Set rngData = ws.Range("A1:E" & lastRow)
    '----- strip any time from OrderDate (column D) ------------------
    For Each c In rngData.Columns(4).Offset(1).Cells
        If IsDate(c.Value) Then c.Value = Int(c.Value)
    Next c
    '----- build 2 × 2 criteria in XFC1:XFD2 --------------------------
    Set rngCrit = ws.Range("XFC1").Resize(2, 2)   'XFC & XFD exist
    rngCrit.Clear
    rngCrit(1, 1).Value = rngData(1, 4).Value     'OrderDate header
    rngCrit(1, 2).Value = rngData(1, 4).Value     'duplicate header
    rngCrit(2, 1).Value = ">=" & startDate
    rngCrit(2, 2).Value = "<=" & endDate
    '----- clear previous in‑place filter ----------------------------
    If ws.FilterMode Then ws.ShowAllData
    '----- apply Advanced Filter -------------------------------------
    rngData.AdvancedFilter _
        Action:=xlFilterInPlace, _
        CriteriaRange:=rngCrit, _
        Unique:=False
    MsgBox "Advanced Filter done – only rows " & _
           "between " & Format(startDate, "d‑mmm‑yyyy") & _
           " and "  & Format(endDate,  "d‑mmm‑yyyy") & _
           " remain visible.", vbInformation
End Sub

➤ Run the macro with  Alt  +  F8  , choose AdvancedFilter_DateInterval, and click Run. Only rows within the chosen date range stay visible.

Use this approach whenever you must limit your data to a particular period without manually applying date filters.

Frequently Asked Questions

How do I filter multiple values in one column using VBA?

Use AutoFilter with an array: Field:=1, Criteria1:=Array(“A”, “B”), Operator:=xlFilterValues to filter multiple values in a single column.

Can I filter data across multiple columns with VBA?

Yes, apply AutoFilter on each column separately. Set the correct Field number for each and apply criteria to filter data across multiple columns simultaneously.

What’s the difference between AutoFilter and AdvancedFilter in VBA?

AutoFilter is easier for simple filters. AdvancedFilter supports complex logic (AND/OR) and allows copying filtered results to another location or applying range-based criteria.

How do I filter by date or number ranges using VBA?

Use AdvancedFilter with criteria like “>=1/1/2024” and “<=12/31/2024” in separate columns to apply numeric or date range filters accurately.

Wrapping Up

Using Excel VBA to filter multiple criteria makes working with data faster and easier. You can quickly show only the information you need by using AutoFilter or AdvancedFilter. These tools help you filter by many values, across columns, or by date and number ranges. Learning these methods saves time and helps you work more efficiently. Try them out to make your Excel tasks simpler and quicker!