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").CurrentRegionis 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.Rangeincludes its header, whileListObject.DataBodyRangerepresents 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
SpecialCellsto 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
CurrentRegionidentified 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
- Excel Text to Columns: How to Split Data
- Excel Text to Columns: How to Enter a Delimiter
- Excel Text to Columns: Can It Split Capital Letters?
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- VBA Dictionary Methods: Add, Exists, Keys and MoreA VBA Dictionary stores pairs of unique keys and associated values, and its core methods are Add, Exists,…
- VBA Excel Send Email in New Outlook: Options That WorkNot directly: Excel VBA’s traditional Outlook automation uses the Outlook COM object model, which New Outlook for Windows…
- VBA Loop Only Through Visible Rows?Yes. In Excel VBA, loop through the rows in your data range and check whether each row is…
- Excel VBA: How to Check Whether a Workbook Is OpenTo check whether a workbook is open in Excel VBA, loop through Application.Workbooks and compare each open workbook’s…
- Excel VBA Try Catch: Error Handling ExplainedExcel VBA does not have a native Try...Catch statement. To handle runtime errors, use On Error GoTo to…
- Excel VBA Set Cell Color by Hex CodeTo set an Excel cell’s fill color from a hex value such as #1E90FF, convert its red, green,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.