Excel VBA AutoFilter: Select Visible Cells Safely

VBA & macros · Updated November 21, 2026

In Excel VBA, use SpecialCells(xlCellTypeVisible) on the filtered data range to get cells left visible by AutoFilter. Exclude the header from that range if you want only matching records; otherwise the header is visible too.

What AutoFilter and visible cells mean

AutoFilter hides rows that do not meet the chosen criteria; it does not delete those records. In VBA, a range can still include both displayed and filtered-out rows, so looping through every cell in the original range does not automatically limit your code to what a person sees. The SpecialCells method narrows a range to cells with a specified type. Its xlCellTypeVisible option returns cells that are not hidden.

“Visible” can include more than filtered rows. Manually hidden rows and hidden columns are also excluded from the returned cells. The result may consist of several separate areas, because visible records can be separated by hidden rows. That matters if you plan to select, copy, format, or loop through the result.

AutoFilter uses a header row to identify each field. Its criteria hide entire records, but the header normally remains displayed. This is why the safest pattern is to apply or confirm the filter on the full list, then call SpecialCells on the data rows below the header.

A basic VBA pattern

For example, suppose a worksheet named Sales has a contiguous list starting at A1, with headings in row 1 and Region in the third column of the list. The following macro filters for West and selects the visible data cells. Save a copy of the workbook before running a macro: VBA changes cannot be undone with Ctrl+Z.

Sub FilterAndSelectVisibleRows()
    Dim ws As Worksheet
    Dim filterRange As Range
    Dim dataRange As Range
    Dim visibleData As Range

    ' The list starts at A1; its first row contains headings.
    Set ws = ThisWorkbook.Worksheets("Sales")
    Set filterRange = ws.Range("A1").CurrentRegion

    If filterRange.Rows.Count < 2 Then
        MsgBox "The list needs a header and at least one data row."
        Exit Sub
    End If

    ' Field numbers are relative to the left edge of filterRange.
    filterRange.AutoFilter Field:=3, Criteria1:="West"
    Set dataRange = filterRange.Offset(1, 0).Resize( _
        filterRange.Rows.Count - 1, filterRange.Columns.Count)

    ' SpecialCells raises an error if no data cells are visible.
    On Error Resume Next
    Set visibleData = dataRange.SpecialCells(xlCellTypeVisible)
    On Error GoTo 0

    If visibleData Is Nothing Then
        MsgBox "No matching data rows are visible."
    Else
        ws.Activate
        visibleData.Select
    End If
End Sub

CurrentRegion finds the contiguous block around A1, so a completely blank row or column can split the list. If the actual list has a known address, such as A1:F500, using that explicit range can be more predictable. Also change the worksheet name, field number, and criterion to fit the workbook. In this example, field 3 means the third column from the left edge of the filtered range, not necessarily worksheet column C.

Why the header and range boundaries matter

The range passed to AutoFilter should include the header and the data. The field number is counted from the first column of that range. If the list starts in column D, for example, its first field is 1 even though it is in worksheet column D. The range passed to SpecialCells can then start one row lower to omit the headings.

Some common ways to define the list are:

  • Range("A1").CurrentRegion is convenient for a compact block with no completely blank rows or columns inside it.
  • An explicit range such as Range("A1:F500") is clearer when the boundaries are known, but it needs updating if records grow beyond the stated range.
  • A table’s ListObject.Range includes its header, while ListObject.DataBodyRange represents its data rows and excludes the header.

A table is often a good choice for a growing list because it expands as records are added. A table with no data rows has no ordinary data body to filter, so code using DataBodyRange should check that it is not Nothing before calling SpecialCells.

Handling no matches and multiple areas

A frequent surprise is that SpecialCells(xlCellTypeVisible) can raise run-time error 1004 when the specified data range has no visible cells. The macro above limits error suppression to that one call, then checks whether the result was set. This is preferable to leaving On Error Resume Next active for the rest of a procedure, where it could conceal unrelated mistakes.

When records remain visible, the returned range may have multiple areas. A range with multiple areas is not always interchangeable with one solid rectangular block. For example, a cell-by-cell operation on the returned range can work, but code that expects one continuous address or one uniform rectangle may need to process each area separately. A common pattern is:

Dim area As Range

For Each area In visibleData.Areas
    ' Work with one continuous visible block at a time.
    Debug.Print area.Address
Next area

In a complete procedure, declare every variable near the top with the other declarations. If you copy visible results to another sheet, consider whether you need to preserve the gaps between visible rows or create a compact list. Those are different outcomes: a filtered selection can be discontiguous, while a compact output generally needs to be assembled deliberately.

Selecting, copying, or processing visible cells

You do not have to select a range to use it. Selecting can help a user see the result, but using the range variable directly is usually more robust for automation. A macro can format visibleData, inspect its cells, or pass it to another procedure without changing the user’s selection. If you do call Select, the relevant worksheet must be active, which is why the example activates ws first.

To copy visible cells, call Copy on the visible range rather than on the original unfiltered list. The source may contain multiple areas, so do not assume that every paste operation will create a compact, uninterrupted table in the exact shape you expect. If the destination must be a clean, consecutive list, a reliable approach is to loop through visible data rows and write their values to the next output row. Decide whether hidden columns should be copied too: xlCellTypeVisible excludes cells in hidden columns as well as filtered-out rows.

For row-level tasks, it can be useful to inspect a key column in the visible result rather than every cell across every field. This reduces unnecessary work and makes the intended operation clearer. Take care with merged cells and formulas, however: their layout and values may not behave like a simple rectangular list.

AutoFilter state and common problems

AutoFilter has two related states that are easy to confuse. AutoFilterMode indicates whether filter arrows are displayed, while FilterMode indicates whether the sheet is currently showing a filtered subset. A sheet can have filter arrows without an active restriction. A macro that depends on particular criteria should set those criteria explicitly rather than assuming the worksheet is already filtered as intended.

Applying a criterion to one field does not necessarily clear filters on other fields. If old criteria remain, the visible result may be narrower than expected. Before changing filter state, decide whether the macro should preserve other field filters or clear them. Clearing filters can change what a user is viewing, so it should be an intentional part of the procedure rather than an incidental step.

  • If the wrong column is filtered, check the left edge of the AutoFilter range and recalculate the relative field number.
  • If the header appears in the selection, apply SpecialCells to the data-only range rather than the full list.
  • If the results are unexpectedly sparse, check for other active criteria, manually hidden rows, and hidden columns.
  • If a range is split at a blank line, check whether CurrentRegion identified the list you intended.

Tables, Excel versions, and platform differences

The VBA pattern applies to desktop Excel for Microsoft 365 on Windows, and the core AutoFilter and SpecialCells concepts also exist in desktop Excel for Mac. Mac keyboard shortcuts for opening the VBA editor differ by keyboard and Excel setup; Windows users commonly use Alt+F11, while Mac users may use Option+F11 or need the Fn key as well. The Developer tab’s Visual Basic command is another way to open the editor when it is enabled.

Excel for the web can display a workbook containing VBA, but it cannot run or edit VBA macros. Open the workbook in desktop Excel to run the procedure. Macro security settings may also block code; enable macros only for files and code you trust. A macro-enabled workbook should be saved in a format that supports VBA, such as .xlsm.

With an Excel table, use its table range rather than relying on a worksheet-wide block if unrelated data sits next to it. Table filter arrows and worksheet AutoFilter behavior are closely related, but table boundaries are explicit and usually easier to maintain as the list grows. For a table with a known name, retrieve it through the worksheet’s ListObjects collection and check that its data body exists before processing visible records.

Frequently Asked Questions

Does SpecialCells(xlCellTypeVisible) include the header? It includes the header if the range you pass includes the header and that header is visible. Use a data-only range, such as the filtered range offset by one row, when you want only records.

Why does SpecialCells give error 1004? One common cause is that no cells in the specified range are visible, such as when the filter has no matches. Handle that call with narrowly scoped error handling and check whether the returned range was set before using it.

Does visible cells mean only rows shown by the filter? No. It also excludes cells in hidden columns and rows hidden manually. If a task needs a particular set of columns regardless of column visibility, select or process those columns intentionally rather than assuming the visible-cell result includes them.

Can I use SpecialCells without selecting the result? Yes. Assign the result to a Range variable and work with that object directly. Selection is optional and usually needed only when you want to show the selected range to the user.

Can Excel for the web run this VBA? No. Excel for the web can open a macro-enabled workbook, but VBA macros must be run in desktop Excel.

Choosing a safe approach

For a one-off task, filtering the list and selecting the visible data-only range is straightforward. For a reusable macro, define the list reliably, set the intended criteria, account for zero matches, and decide how to handle multiple areas and hidden columns. Using a table can make the range easier to maintain; using a range variable instead of relying on selection makes the code easier to automate. Finally, save a backup before running code that changes or copies workbook data, because Excel’s Undo command does not reverse VBA actions.

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.