[Excel VBA] How to Filter with Multiple Conditions Using AdvancedFilter [Complete Sample Code]

Overview

Excel’s AdvancedFilter allows for flexible data extraction combining AND/OR conditions by setting a “Criteria Range” that reuses column headers.

This article provides sample code to execute In-Place Filtering (only changing the display) from VBA and explains how to create the criteria range and what to watch out for.

Prerequisites

• Excel: Microsoft 365 or 2016 and later.
• Source Data: A named range InvoiceData is set on the “Data” worksheet.
• Criteria Range: Range F2:G4 (including headers) on the “Criteria” worksheet.
• Macro Location: Standard Module.

Configuration Rules for the Criteria Range

To set up the criteria, follow these rules:

Cell	Setting Content
F2 / G2	Copy the column headers from the source data (e.g., Region, Amount).
F3	"East" (Strings should be enclosed in double quotes).
G3	>10000
F4	"West"
G4	>5000

• Same Row = AND Condition: F3 and G3 mean “Region column is East AND Amount column is greater than 10,000″.
• Different Rows = OR Condition: Since F4 and G4 are on a different row, they are combined with the above AND condition using OR.

Sample Code (VBA)

Sub RunAdvancedFilter()

    Dim srcRange   As Range     ' Data to be filtered
    Dim critRange  As Range     ' Criteria range
    
    '--- Set Range Objects ---
    Set srcRange  = Worksheets("Data").Range("InvoiceData")
    Set critRange = Worksheets("Criteria").Range("F2:G4")
    
    '--- Execute AdvancedFilter in place ---
    srcRange.AdvancedFilter _
        Action:=xlFilterInPlace, _
        CriteriaRange:=critRange
    
    MsgBox "Data has been filtered based on the specified conditions.", vbInformation

End Sub

Code Explanation

• Line 6: InvoiceData refers to the entire source data (including the header row).
• Line 9: CriteriaRange is set to F2:G4. It is mandatory to include the headers and the criteria in this range.
• Line 11: Specifying xlFilterInPlace switches the visible rows while maintaining the original data structure.

Application Tips

Transferring Extraction Results to Another Sheet

If you want to copy the results instead of filtering in place, change the Action and add CopyToRange.

srcRange.AdvancedFilter _
    Action:=xlFilterCopy, _
    CriteriaRange:=critRange, _
    CopyToRange:=Worksheets("Result").Range("A1")

Expanding the Criteria Range Dynamically

If the number of criteria changes, you can make the code flexible by getting the last row of the criteria sheet and re-setting critRange dynamically.

Clearing the Filter

To clear the filter and show all data:

If Worksheets("Data").FilterMode Then Worksheets("Data").ShowAllData

FAQ

Q: What happens if the headers in the criteria range do not exactly match the source data?

A: Columns that do not match will be ignored. To prevent errors, always use Copy & Paste to create headers.

Q: Can I use formulas in the criteria?

A: Yes. If you enter a formula in a criteria cell, only rows where the result is TRUE will be extracted.

Summary

By operating AdvancedFilter from VBA, you can combine complex AND/OR conditions without writing complex logic in your code.

Please run the sample code and verify that the data is extracted correctly according to the criteria range. Since you can change the logic simply by adding or deleting criteria rows, this is very useful for improving the efficiency of regular reports and data analysis.