To loop through only visible data rows in Excel VBA, apply SpecialCells(xlCellTypeVisible) to a one-column range that excludes the header, then process each returned area. This works for rows hidden by AutoFilter and rows hidden manually; the example below also handles the non-contiguous ranges filters create.
What Excel Means by Visible Rows
A row is visible when it is not hidden on the worksheet. An AutoFilter hides records that do not meet its criteria, and users can also hide rows directly. Both kinds of hidden rows are excluded by xlCellTypeVisible. Hidden columns are a separate matter: this method concerns which cells and rows are visible, not whether every column in a record is shown.
Filtering often makes the data range non-contiguous. For example, matching records in rows 2, 5, and 9 may be separated by hidden rows. Excel represents those visible sections as separate areas. A loop that assumes all visible rows form one continuous block can therefore skip records or handle the range incorrectly.
Use a single, unhidden anchor column for the visible-cell range. The column does not have to contain a value in every row for the visibility test, but it should be unhidden; choose a column that identifies the data boundary reliably when calculating the last row.
A Macro That Processes Visible Data Rows
This example assumes a worksheet named Data, headers in row 1, and records beginning in row 2. Column A is the unhidden anchor column, and columns A and B contain values to inspect. The macro prints the row number and its first two values to the Immediate window; replace that action with the work your task requires.
Option Explicit
Sub LoopThroughVisibleRows()
Dim ws As Worksheet
Dim lastRow As Long
Dim dataRange As Range
Dim visibleCells As Range
Dim area As Range
Dim cell As Range
Set ws = ThisWorkbook.Worksheets("Data")
' Find the last used row in the anchor column.
lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
If lastRow < 2 Then Exit Sub
' Exclude the header; use one unhidden column as the anchor.
Set dataRange = ws.Range(ws.Cells(2, 1), ws.Cells(lastRow, 1))
' SpecialCells can raise an error if no cells are visible.
On Error Resume Next
Set visibleCells = dataRange.SpecialCells(xlCellTypeVisible)
On Error GoTo 0
If visibleCells Is Nothing Then Exit Sub
' A filtered result may contain multiple separate areas.
For Each area In visibleCells.Areas
For Each cell In area.Cells
Debug.Print cell.Row, ws.Cells(cell.Row, 1).Value, _
ws.Cells(cell.Row, 2).Value
Next cell
Next area
End Sub
The nested loops are deliberate: the outer loop visits each contiguous visible area, and the inner loop visits its cells. Because the anchor range has one cell per data row, each visible cell identifies one row to process. Using cell.Row lets the macro read or update other columns on that same worksheet row.
How to Run the Macro
- Save a copy of the workbook before running a macro. VBA changes cannot be reversed with Ctrl+Z.
- In desktop Excel for Microsoft 365 on Windows, open the VBA editor with Alt+F11. In the editor, choose Insert > Module, then paste the code into the module.
- Change
Datato the exact worksheet tab name. Confirm that row 1 is the header and the data starts in row 2. If your layout differs, adjust those references. - Return to Excel and run
LoopThroughVisibleRowsfrom Developer > Macros. If the Developer tab is not shown, enable it in Excel Options > Customize Ribbon. - To see the example output, open the VBA editor and choose View > Immediate Window. The window displays the worksheet row number and the values from columns A and B for each visible record.
On Mac, desktop Excel supports VBA, but keyboard shortcuts can vary with the keyboard and system settings; use the Visual Basic command on the Developer tab if Alt+F11 is unavailable. Excel for the web can open a macro-enabled workbook, but it cannot run or edit VBA macros. Use desktop Excel to execute this code.
Using the Macro with AutoFilter and Tables
For a normal filtered range, apply the filter to the full dataset, including its header, but pass only its data rows to the loop. The sample calculates its boundary from column A and starts at row 2, so the heading is never processed. If you have blank cells in column A, the macro still visits those rows if they fall within the calculated range. However, a blank at the bottom of column A can make the last-row calculation stop too early. In that case, choose a column that is consistently filled, or determine the data boundary from another reliable source.
Excel Tables are often a better choice for records that grow over time because the table expands as rows are added. In VBA, a table is a ListObject, and its data rows are available through DataBodyRange; that range excludes the header. You can use a single, unhidden table column from DataBodyRange as the anchor, then apply SpecialCells(xlCellTypeVisible) and loop through its areas as in the example. Check whether DataBodyRange is Nothing before using it, because a table with no data rows has no body range.
When a filter leaves no visible data records, SpecialCells may raise a run-time error rather than return a usable range. The short On Error Resume Next block in the macro handles that possibility and is immediately turned off. Keep error suppression limited to the statement that can fail; leaving it enabled for the rest of a procedure can hide unrelated bugs.
Choosing Between SpecialCells and a Row-by-Row Check
SpecialCells is useful when you want Excel to supply the visible cells and expect the result to be reasonably large. It handles filtered and manually hidden rows without needing to reproduce the filter criteria in VBA. Its main complication is that the returned range can have multiple areas, so code should not assume a single continuous block.
For a small worksheet, a direct row loop is easier to understand and avoids the no-visible-cells case. For example, loop from the first data row to the last and process a row only when Not ws.Rows(rowNum).Hidden is true. The Hidden property also reflects rows hidden by filtering. This approach checks every row in the range, including hidden ones, so it can be slower on a large dataset. It also does not require the anchor column to be visible.
Do not rely on a simple loop over Selection.Rows to represent every visible row after filtering. A filtered range can have multiple areas, and range properties used on a multi-area selection can behave differently than expected. Explicitly iterating through Areas makes the structure clear and avoids depending on the current selection.
Common Mistakes and Practical Safeguards
- Including the header: Start the data range below the heading row. Otherwise, the header is visible and may be processed as a record.
- Using a hidden anchor column:
SpecialCells(xlCellTypeVisible)returns visible cells, so a hidden anchor column may yield no cells even when worksheet rows are visible. Choose an unhidden column, or use the row-by-rowHiddencheck. - Ignoring multiple areas: Filtered results can be discontinuous. Loop over
visibleCells.Areasand then over the cells in each area, as shown. - Assuming column A always sets the last row: If records can have blank cells in that column, calculate the final row using a dependable field or another boundary method. The example uses column A because its data layout is explicitly assumed.
- Changing data while iterating: If the macro deletes rows or changes filter conditions, the visible range can shift while it is being processed. For deletion tasks, consider collecting the target row numbers first or working from the bottom upward, and test on a copy.
- Expecting Ctrl+Z to restore macro edits: Excel clears the normal undo history when VBA runs. Save a backup before a macro that edits or deletes workbook data.
Adapting the Loop to Perform Real Work
The sample uses Debug.Print so it demonstrates which rows qualify without changing worksheet data. To perform an action, put it inside the inner loop and use cell.Row to address the current record. For example, a macro could copy a visible row’s value into a review column, calculate a result, or build a report. Use explicit worksheet references such as ws.Cells(cell.Row, 3) rather than relying on whichever sheet happens to be active.
Be precise about what the task means by process. If you want to copy only visible records to another sheet, loop through the visible cells and copy the corresponding full row or chosen fields. If you only want to change values in visible cells of a specific column, apply SpecialCells to that column’s data range instead. The range passed to SpecialCells controls which cells are returned; a one-column anchor is convenient for identifying rows, while a multi-column range returns visible cells across those columns.
For large datasets, avoid repeatedly selecting cells or activating sheets. Direct references through ws are less dependent on user interface state and generally make the procedure easier to maintain. If the macro changes filters, visibility, or row positions during processing, define the intended behavior first: should newly visible rows be included, or should the macro process only the rows that were visible when it began?
Frequently Asked Questions
Does xlCellTypeVisible include manually hidden rows?
No. It returns visible cells, so rows hidden manually or by an AutoFilter are excluded. The same distinction applies if you use the row-by-row check: process a row only when its Hidden property is false.
How do I skip the header row?
Start the range at the first data row rather than the header. In the example, row 1 is the header and the range begins at row 2.
Why does SpecialCells cause an error when every record is filtered out?
There may be no visible cells in the specified data range, and Excel can raise an error for that request. Handle the error around the SpecialCells line, then check whether the resulting range is Nothing, as the sample does.
Can I process visible rows if the first column is hidden?
Use another unhidden column as the one-column anchor, or loop through row numbers and test ws.Rows(rowNum).Hidden. A hidden anchor column is not a dependable source of visible cells.
Can this VBA run in Excel for the web?
No. Excel for the web does not run VBA macros. Open the workbook in desktop Excel for Windows or Mac to use the macro.
Summary
For a filtered worksheet, use a header-free range in an unhidden anchor column, call SpecialCells(xlCellTypeVisible), and iterate through each returned area. Handle the possibility that no visible cells remain, verify the range boundaries, and save a backup before running code that changes data. For a small range or a hidden anchor column, checking each row’s Hidden property is a straightforward alternative.
Related
- Excel Text to Columns: Split Alt+Enter Line Breaks
- Excel Text to Columns and Rows: How to Split Data
- Excel Text to Columns: How to Make Results Vertical
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Excel VBA Function Arguments: Syntax and ExamplesIn Excel VBA, function arguments are the values or variables you pass to a function so it can…
- Excel VBA Visible Worksheet Property: How to Set ItIn Excel VBA, a worksheet’s visibility is controlled by its Visible property. Set it to xlSheetVisible to show…
- Convert Excel Macros to Google Sheets: What Works?Yes, you can move an Excel macro workflow to Google Sheets, but you generally cannot run its VBA…
- Excel VBA Function Not Updating Automatically? Fix ItIf an Excel VBA function is not updating automatically, first check Formulas > Calculation Options > Automatic, then…
- Excel VBA: Copy and Paste Range ValuesTo copy a range’s results without its formulas or formatting, assign the source range’s Value property to a…
- Excel VBA: Highlight Cells in an Array LoopTo highlight worksheet cells while looping through an array in Excel VBA, read a range into an array,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.