Excel VBA Advanced Filter: Ignore Blank Criteria and Allow Multiple Values
Question details
Apply Excel Advanced Filter with VBA so users can select multiple colors and leave other criteria optional without unintentionally returning all records.

- Product
- Microsoft Excel desktop with VBA
- Device & OS
- not provided
- Scenario
- Users select one or more colors and optionally supply additional conditions. The filter should update when these inputs change.
- Observed behavior
- Blank rows in the criteria range cause Advanced Filter to return all records instead of restricting results to the selected values.
Use a macro-enabled workbook (.xlsm) in desktop Excel, and enable macros only if you trust the workbook. Check that your criteria headers exactly match the data headers and reserve an unused area for the generated criteria range.
Build a compact criteria range before running Advanced Filter
Use this VBA approach to omit unused criteria rows and combine multiple selected colors with optional Size and Region conditions.
Advanced Filter combines conditions on the same row with AND and combines separate criteria rows with OR. An entirely blank criteria row therefore acts as an unrestricted alternative; a blank cell within an otherwise populated row simply leaves that field unrestricted.
This example uses a table named DataTable with exactly three columns, in this order: Color, Size, and Region. Color choices go in H2:H6, the optional Size goes in K2, and the optional Region goes in L2. The macro generates criteria in N1:P6, repeating the optional conditions for every selected color.
Select your data, choose Insert > Table, and confirm that the table has headers. Under Table Design, set Table Name to DataTable and turn off Total Row. Use Color, Size, and Region as the three column headers. Enter color selections in H2:H6 and optional Size and Region values in K2 and L2. Keep N1:P6 unused.
Open Developer > Visual Basic, choose Insert > Module, and paste this code into the standard module: Public Sub ApplyOptionalFilter(ByVal ws As Worksheet) Dim src As Range, cell As Range Dim nextRow As Long, lastRow As Long, r As Long Dim sizeText As String, regionText As String Set src = ws.ListObjects("DataTable").Range sizeText = Trim$(CStr(ws.Range("K2").Value2)) regionText = Trim$(CStr(ws.Range("L2").Value2)) ws.Range("N1:P6").ClearContents ws.Range("N1:P1").Value = src.Rows(1).Value nextRow = 2 For Each cell In ws.Range("H2:H6") If Len(Trim$(CStr(cell.Value2))) > 0 Then ws.Cells(nextRow, "N").Value = "=" & Trim$(CStr(cell.Value2)) nextRow = nextRow + 1 End If Next cell If nextRow = 2 Then If Len(sizeText) = 0 And Len(regionText) = 0 Then If ws.FilterMode Then ws.ShowAllData Exit Sub End If nextRow = 3 End If lastRow = nextRow - 1 For r = 2 To lastRow If Len(sizeText) > 0 Then ws.Cells(r, "O").Value = "=" & sizeText If Len(regionText) > 0 Then ws.Cells(r, "P").Value = "=" & regionText Next r src.AdvancedFilter Action:=xlFilterInPlace, _ CriteriaRange:=ws.Range("N1:P" & lastRow), Unique:=False End Sub
In the Visual Basic Editor, double-click the worksheet containing DataTable under Microsoft Excel Objects. Paste this event procedure into that worksheet module, not the standard module: Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Me.Range("H2:H6,K2:L2")) Is Nothing Then Exit Sub On Error GoTo Failed Application.EnableEvents = False ApplyOptionalFilter Me Application.EnableEvents = True Exit Sub Failed: Application.EnableEvents = True MsgBox "Filter could not be applied: " & Err.Description, vbExclamation End Sub
Return to Excel and enter two colors in H2:H6, leaving gaps between them. Set K2 to a size and leave L2 blank. Confirm that the results include either selected color, always with the specified size. Then clear H2:H6 and K2:L2 to show all records.

Remove unused criteria rows when filtering manually
Use this method to verify the filtering logic before adding VBA or when automatic updates are unnecessary.
Use WPS Spreadsheet for Everyday Excel-Compatible Filtering
WPS Office is a free, lightweight alternative with a familiar spreadsheet interface and Excel format compatibility for easy migration. Use WPS Spreadsheet for routine filtering of Excel data; it does not repair Excel VBA, and macro support depends on your WPS edition and platform.

Frequently Asked Questions
Why does one blank criteria row make Advanced Filter return everything?
Advanced Filter treats separate criteria rows as OR alternatives. A completely blank row places no restrictions on records, so it can match every record. In Data > Advanced, exclude unused rows from Criteria range, or have VBA build a compact range.
Can I leave an optional criteria cell blank?
Yes. A blank cell within an otherwise populated criteria row leaves that field unrestricted. For example, a row containing Color = Red with an empty Region cell matches red records from any region. Avoid including completely blank rows.
How do I combine several colors with one required size?
Put each color on a separate criteria row and repeat the required size on every row. Conditions within each row use AND, while the rows use OR. The provided macro does this using colors from H2:H6 and the size from K2.
How do I clear the filter using this VBA example?
Select H2:H6 and K2:L2, then press Delete to clear their contents. Worksheet_Change runs the procedure, which calls ShowAllData when no criteria remain. Clearing only the colors leaves any Size or Region condition active.
Why does changing a formula-based criterion not run the macro?
Worksheet_Change responds to direct edits and pasted values, not changes caused solely by formula recalculation. For formula-driven inputs, call ApplyOptionalFilter from a suitable Worksheet_Calculate event or a button, with safeguards against repeated execution.




