To show rows where a column is not blank, apply AutoFilter to the full table or data range and use the criterion <>. In VBA, that is Criteria1:="<>"; for example, filterRange.AutoFilter Field:=2, Criteria1:="<>" filters the second column of the selected range to show nonblank cells.
What the Not-Blank Criterion Means
Excel AutoFilter uses comparison criteria to decide which records to display. The criterion <> means “not equal to blank” in this context, so rows with a value in the chosen field remain visible and rows treated as blank are hidden. It is not a VBA operator that you apply to each cell: it is a text criterion passed to the AutoFilter method.
The Field number is relative to the range receiving the filter, not the worksheet’s column number. If the range starts in column A, Field 2 means column B. If the range starts in column C, Field 2 means column D. The range should include the header row, because AutoFilter uses that first row for field names.
Basic VBA Example
This macro assumes the worksheet is named Data, the header row is row 1, the data occupies A1:D100, and the field to check is the second column of that range. Adjust the sheet name, range, and field number to match the workbook. Save a copy before running any macro: VBA actions generally cannot be undone with Ctrl+Z.
Sub FilterRowsWithNonBlankValues()
Dim ws As Worksheet
Dim filterRange As Range
Set ws = ThisWorkbook.Worksheets("Data")
Set filterRange = ws.Range("A1:D100")
' Field 2 is column B because the range begins in column A.
filterRange.AutoFilter Field:=2, Criteria1:="<>"
End Sub
Run the macro with the target workbook active and macros enabled. The filter arrows appear if they were not already present, and the chosen field is restricted to records matching the criterion. If the range already has filters, use the same range that owns those filters; applying AutoFilter to a different range can produce confusing results or an error.
Choose the Correct Range and Field
A common reason for a filter to appear ineffective is an incorrect range or field index. A range that omits the header row can treat the first data record as a header. A range that stops before the last row leaves records outside the filter untouched. A field number that is off by one filters a neighboring column instead.
For example, with headers in A1:E1 and records below, use A1:E500 as the filter range. To check column D, use Field 4. If the range instead begins at C1, column D is Field 2. The worksheet row numbers do not determine the field index; only the position within the filter range does.
- Include one header row and all records that should participate.
- Keep the data in a consistent table-like layout, with one record per row and no merged cells within the data.
- Check that the chosen field contains the values you mean to test, rather than a visually adjacent column.
- For a growing data set, consider converting the range to an Excel Table so new rows become part of the table automatically.
Blank Cells, Formula Results, and Spaces
“Blank” can mean different things in a spreadsheet. A genuinely empty cell contains no value or formula. A formula cell may display an empty string, often because a formula returns "". A cell may also contain one or more spaces, which look empty but are text characters. These cases can affect whether a row matches the filter in the way a person expects.
For an ordinary data column, <> is the standard AutoFilter criterion for showing nonblank entries. If cells contain formulas that display nothing, inspect the filtered result rather than assuming that visual emptiness and an actually empty cell are identical. If your requirement is specifically “show rows where the displayed text has at least one character,” a helper column can express that rule explicitly.
With the value being checked in A2 and the helper formula entered in B2, use =LEN(A2)>0, then fill the formula down. The formula returns TRUE for a value with one or more characters and FALSE for an empty string. Filter the helper column to TRUE. A cell containing only spaces still returns TRUE, because spaces have a length; if spaces should count as blank, clean the source data or use a suitable trim-based test and consider whether it must handle nonbreaking spaces as well.
Using AutoFilter with an Excel Table
Tables are often easier to filter than fixed ranges because they expand as records are added and provide named columns. If the data is an Excel Table named SalesTable and the second table column is the field to check, the table range includes its header row. A macro can use the table’s range directly:
Sub FilterTableNonBlank()
Dim ws As Worksheet
Dim salesTable As ListObject
Set ws = ThisWorkbook.Worksheets("Data")
Set salesTable = ws.ListObjects("SalesTable")
' Field 2 means the second column within this table.
salesTable.Range.AutoFilter Field:=2, Criteria1:="<>"
End Sub
Replace the worksheet and table names with those shown in the workbook. The field number is still positional within the table. If a table column is moved, a hard-coded field number may point to a different column, so confirm the layout whenever the macro is revised. A missing or misspelled table name causes a VBA error when the macro tries to set the ListObject variable.
Working with Existing Filters
AutoFilter has two related states: the filter controls can be present, and one or more filter criteria can be active. A macro that applies a new criterion may interact with criteria already set on other fields. That can be useful when you want to narrow the current results further, but it can surprise users who expect the macro to reset every filter first.
Decide whether the macro should preserve other filters or clear them. Clearing all criteria changes the user’s current view, so do not do it silently in a shared workbook. If you need a clean result, make that behavior explicit and test it on a copy. Also check whether the worksheet is protected; protection settings can prevent filtering or changing filter criteria unless the sheet was configured to allow those actions.
When a filter is already applied to a range, keep the macro’s range aligned with that existing AutoFilter. Reapplying a criterion to the same field replaces that field’s criterion, while criteria on other fields may remain active. To show all rows again, use the worksheet’s Data tab filter controls or write a deliberate reset step; avoid turning filter arrows off merely to clear a criterion if users still need the controls.
Common Problems and How to Fix Them
- No rows appear: Confirm the field number, source range, and actual cell contents. Other active filters may also be excluding records.
- Some empty-looking rows remain: Check for formulas returning empty strings or spaces. Use a helper formula if the rule is about displayed text rather than physical cell emptiness.
- Rows below the data are unchanged: Extend the range to include them or use a Table that expands with new records.
- The wrong column is filtered: Recalculate Field relative to the first column of the range. It is not the worksheet column letter.
- The macro reports an error: Verify the sheet and table names, confirm that the range includes a header, and check that the workbook is not protected in a way that blocks filtering.
- The filter seems to remove too many rows: Inspect the source values and other active criteria. Filtering is cumulative across fields, so a row must satisfy all active field filters to remain visible.
For a quick diagnosis, click the filter arrow on the target column and examine the available values and the blank option. This helps distinguish a VBA range problem from a data-quality problem. You can also temporarily clear other filters from the Data tab and apply the criterion manually to confirm that the column itself behaves as expected.
Excel Versions and Macro Limits
The VBA AutoFilter method is available in desktop Excel, including Microsoft 365 for Windows and Mac, though the VBA editor and macro security prompts may look different between platforms. Excel for the web can display workbooks containing macros, but it cannot run VBA macros. To execute this code, open the workbook in desktop Excel.
Save a workbook containing VBA in a macro-enabled format such as .xlsm; saving as a standard .xlsx file does not preserve VBA code. Only enable macros in workbooks you trust, because macros can make changes to workbook data and perform other actions. If you distribute the workbook, explain what the macro changes and where it should be run.
Frequently Asked Questions
What is the VBA AutoFilter criteria for not blank?
Use Criteria1:="<>" in the AutoFilter call. The field number identifies the column’s position inside the filter range.
Does AutoFilter Field use worksheet column numbers?
No. Field is counted from the first column in the specified AutoFilter range. If the range begins in column C, its first field is column C, regardless of the worksheet’s column letters.
How do I filter a column to show blanks instead?
Use the filter dropdown and select the blank entry, or apply the blank criterion Criteria1:="=" to the intended field. Check formula results and spaces if the displayed result does not match what you consider blank.
Will the not-blank filter include formulas that return an empty string?
Do not assume that a formula cell displaying nothing is equivalent to a physically empty cell for every filtering task. If the rule must be based on visible text, use a helper test such as =LEN(A2)>0 and filter that helper column for TRUE.
Can Excel for the web run this VBA macro?
No. Excel for the web does not run VBA. Open the workbook in desktop Excel for Windows or Mac to run the macro, and save it in a macro-enabled file format if the code must be retained.
Related
- Excel Split One Cell Into Multiple Rows: How It Works
- Excel Split Print Area Into Two Pages: Page Breaks
- Excel Split Multiline Cell Into Rows
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- VBA Dictionary Functions and How to Use ThemIn VBA, a Dictionary is an object for storing unique keys alongside values, and its main functions are…
- Excel VBA Current Date: Date, Now, Formatting, and ExamplesIn Excel VBA, use Date to get the computer’s current date, without a time; use Now to get…
- VBA Loop Types: For, Do, and While ExplainedVBA has four commonly encountered loop constructs: For…Next, For Each…Next, Do…Loop, and While…Wend. Use For…Next when a counter…
- Excel VBA Type Mismatch: Causes and FixesIn Excel VBA, “Run-time error 13: Type mismatch” means VBA tried to use a value as a different…
- Excel VBA Find Last Populated Row: Reliable MethodsIn Excel VBA, use ws.Cells(ws.Rows.Count, "A").End(xlUp).Row to find the last nonempty cell in column A. If you need…
- VBA Remove AutoFilter: Clear Filters or Remove ArrowsTo clear the active filters on a worksheet but keep its dropdown arrows, use If ws.FilterMode Then ws.ShowAllData.…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.