VBA AutoFilter Not Blank: Filter Nonempty Cells

VBA & macros · Updated November 18, 2026

To use VBA AutoFilter to show only nonblank cells, set the field’s criterion to <>: dataRange.AutoFilter Field:=2, Criteria1:="<>". The field number is relative to the filtered range, so this example filters its second column; include the header row in that range.

What “not blank” means in AutoFilter

AutoFilter hides rows that do not match a criterion; it does not delete their data. In VBA, Criteria1:="<>" means “does not equal an empty value” and is the usual way to display records with a value in the selected field. The matching rows remain in place, and you can later clear the filter to show the full list again.

A blank-looking cell is not always an empty cell. It may contain a formula that displays an empty string, a space, or another invisible character. A cell containing a space has content, even though it may look empty on screen. This distinction matters if the result of the filter must follow a precise business rule, such as “contains a name after trimming spaces,” rather than Excel’s ordinary blank/nonblank filtering behavior.

The familiar worksheet equivalents are the filter menu’s (Blanks) and nonblank choices. In VBA, use "=" as the criterion to request blanks and "<>" to request nonblanks. Those criteria are not VBA operators or formula fragments; they are criteria strings passed to Excel’s AutoFilter method.

A basic macro for nonblank cells

This example assumes a worksheet named Data with a contiguous list starting in A1, headers in row 1, and the field to inspect in the second column of that list. CurrentRegion finds the connected block around A1. Change the worksheet name, starting cell, and field number to match your workbook.

Sub FilterNonblankCells()
    Dim ws As Worksheet
    Dim dataRange As Range

    ' Set the worksheet and the contiguous list, including its headers.
    Set ws = ThisWorkbook.Worksheets("Data")
    Set dataRange = ws.Range("A1").CurrentRegion

    ' Show rows where the second field is not blank.
    dataRange.AutoFilter Field:=2, Criteria1:="<>"
End Sub

Before running any macro, save a copy of the workbook. VBA actions cannot be undone with Ctrl+Z, and a filtering macro can affect what is visible in the current sheet. Open the Visual Basic Editor, insert the procedure in a standard module, and run it while the workbook containing the Data sheet is active. If macros are blocked by your organization’s security settings, follow its approved process rather than changing security settings casually.

Choose the correct range and Field number

The most common cause of an apparently ineffective filter is an incorrect range or field index. The AutoFilter range must include the header row and the data rows. Field:=1 means the first column inside that range, not necessarily worksheet column A. If the range begins at C1, then Field 1 refers to column C, and Field 2 refers to column D.

For example, if a list occupies C1:F200 and the “Email” header is in column E, the email field is the third field in the range. Use Field:=3. Filtering field 5 would be invalid because the selected range contains only four columns. Keep the range aligned with the list: including unrelated columns can shift field numbers, while excluding the header can cause Excel to treat the first data row as a header.

CurrentRegion is convenient when the list is a single uninterrupted block. It can stop at a completely empty row or column, however, and may include adjacent data if there is no blank separator. If the data has gaps or neighboring content, specify the intended range directly, such as ws.Range("A1:D500"), or use an Excel Table. Inspect the range before relying on it in a recurring process.

Filter blank cells instead

To display rows whose selected field is blank, use Criteria1:="=" instead. The structure is otherwise the same. In this example, the list still starts at A1 and the first field is the one being checked.

Sub FilterBlankCells()
    Dim ws As Worksheet
    Dim dataRange As Range

    ' Set the worksheet and include the header row in the range.
    Set ws = ThisWorkbook.Worksheets("Data")
    Set dataRange = ws.Range("A1").CurrentRegion

    ' Show rows where the first field is blank.
    dataRange.AutoFilter Field:=1, Criteria1:="="
End Sub

To show every row again, clear the filter rather than deleting rows. The Data tab’s Clear command removes the active filter criteria while leaving the records intact. In a macro, the worksheet method ShowAllData can show all rows when the sheet is currently filtered; check that a filter is active before calling it, because calling it when no rows are filtered can raise an error.

Formula results, spaces, and invisible characters

A formula that returns "" displays nothing, but the cell still contains a formula. That is different from a cell that has never contained a value. The way formula-empty cells appear in filter choices or respond to blank criteria can depend on the data and Excel’s handling of the filtered values. If these cells are important, test the filter on a small copy of the actual data rather than assuming every visually empty cell is treated identically.

Spaces and nonprinting characters create another common mismatch. A cell containing one ordinary space is not truly empty, so a not-blank filter can retain its row. Imported data can also contain nonbreaking spaces, which look like ordinary spaces but are not removed by every cleanup method. If “not blank” should mean “contains at least one meaningful character,” a helper column can apply that rule explicitly.

With source values in A2:A100, put this formula in a helper column on row 2, fill it down, and filter the helper field for TRUE. For example, if A2 contains only ordinary spaces, the result is FALSE; if it contains “North,” the result is TRUE.

=LEN(TRIM(A2))>0

TRIM removes leading and trailing ordinary spaces and reduces repeated internal ordinary spaces to one; it does not remove every kind of invisible character. If your source uses nonbreaking spaces or other imported characters, clean those values with a method suited to the source before deciding which rows count as populated. A helper formula makes the rule visible and auditable, while AutoFilter simply applies the selected condition to that helper field.

Using AutoFilter with an Excel Table

For a well-maintained list, an Excel Table can be easier to work with than a manually sized range. Tables expand when rows are added and provide stable column names. You can refer to the table’s range through a ListObject and still pass a field number to AutoFilter. The field number remains relative to the table range: the first table column is Field 1.

Sub FilterTableNonblank()
    Dim ws As Worksheet
    Dim dataTable As ListObject

    ' Replace Data and Table1 with the actual worksheet and table names.
    Set ws = ThisWorkbook.Worksheets("Data")
    Set dataTable = ws.ListObjects("Table1")

    ' Filter the second table column to show nonblank values.
    dataTable.Range.AutoFilter Field:=2, Criteria1:="<>"
End Sub

Using a table avoids guessing the final row, but it does not make an incorrect field number correct. If the intended column is “Status,” verify its position in the table. For code that must survive column reordering, find the column’s position by its header name before applying the filter, rather than hard-coding the number. Also confirm that the table exists and that its name is spelled exactly as it appears in Table Design.

Common problems and practical checks

  • The wrong rows appear: Confirm the header row, the range’s first column, and the Field number. Remember that Field counts from the left edge of the AutoFilter range.
  • The macro errors on the worksheet line: Check that the sheet name is correct and belongs to ThisWorkbook. If the code is stored in another workbook, ThisWorkbook still refers to the workbook containing the code.
  • Only part of the list is filtered: Check for blank rows or columns that split a CurrentRegion, or use an explicit range or table instead.
  • Rows with empty-looking cells remain: Inspect a sample cell in the formula bar. It may contain a formula, spaces, or imported characters rather than being genuinely empty.
  • The filter seems to replace another filter: AutoFilter criteria apply to a particular range and field. Make sure the range matches the existing list and consider which other field filters should remain active.
  • Nothing changes visually: The data may already contain only matching rows, the selected field may be wrong, or the filtered rows may be outside the visible area. Check the dropdown indicator and the row numbers after running the macro.

For diagnosis, first apply the filter manually to a copy of the data and compare the visible rows with the VBA result. That separates a data-definition issue from a macro issue. Also verify whether the first row really contains headers; AutoFilter expects a header row in the range, and merged or duplicated headers can make lists harder to manage reliably.

When a helper column is the better choice

AutoFilter is best when the requirement is simply to show or hide records based on whether one field is blank. A helper column is preferable when “blank” has a custom meaning: for example, formula output should count as empty, whitespace should be ignored, or several columns must be checked together. The helper lets you define that test once and filter its TRUE/FALSE result.

For a one-time review, the worksheet filter menu may be faster than writing VBA. For a repeatable report or import-cleaning routine, VBA can apply the same criterion consistently. A formula-driven report using Excel’s FILTER function is another option in Microsoft 365 and Excel 2021 or later, but it returns matching results in a separate area rather than hiding original worksheet rows. Choose based on whether the task is to inspect the existing list, automate a recurring filter, or create a separate output.

Frequently Asked Questions

What is the VBA criteria for AutoFilter not blank?
Use Criteria1:="<>" in the AutoFilter call. Set Field to the selected column’s position within the filtered range.

How do I filter for blank cells with VBA?
Use Criteria1:="=". Include the header row and select the field whose blank values you want to display.

Does Field refer to the worksheet column letter?
No. Field is a number counted from the first column of the range passed to AutoFilter. A range beginning in column C has its first field in worksheet column C.

Why does VBA include a cell that looks empty?
The cell may contain a formula returning an empty string, a space, or an invisible imported character. Inspect the cell and, when necessary, use a helper column to define exactly what should count as populated.

Can I undo an AutoFilter macro with Ctrl+Z?
No. Excel clears its normal undo history when VBA runs, so Ctrl+Z will not reverse the macro. Save a copy before running code that changes the workbook’s view or data.

Related

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.