VBA & Macro Problems

Excel VBA Advanced Filter: Ignore Blank Criteria and Allow Multiple Values

Huda QurayshiHuda Qurayshi Nov 13, 2026 869 views

Question details

Apply Excel Advanced Filter with VBA so users can select multiple colors and leave other criteria optional without unintentionally returning all records.

Excel VBA Advanced Filter: Ignore Blank Criteria and Allow Multiple Values
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.
Before you start

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.

Solution 1Recommended

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.

1
Prepare the table and input cells

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.

2
Add the filtering procedure

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

3
Run the procedure when inputs change

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

4
Test multiple values and optional conditions

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.

Build a compact criteria range before running Advanced Filter
Adapt the example to your workbook layout: Change the table name, input addresses, scratch range, and field mapping together. This example treats inputs as text equality criteria; Excel wildcard characters such as * and ? still have special meaning. Formula recalculation does not trigger Worksheet_Change.
Free Microsoft Office alternative

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.

Filter spreadsheet records through column filter menus for everyday color, size, or region selections.Open and edit Microsoft Excel .xlsx and .xls files.Work with familiar worksheet layouts and spreadsheet controls.Move routine spreadsheet tasks to a lightweight Office suite without rebuilding ordinary data tables.
QA img-9

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.