VBA Loop Only Through Visible Rows?

Yes. In Excel VBA, loop through the rows in your data range and check whether each row is hidden; process it only when EntireRow.Hidden is False. This skips rows hidden by AutoFilter and rows hidden manually, while leaving the worksheet’s filter and display settings unchanged.

A safe row-by-row example

The example below assumes a worksheet named Data, a header in row 1, and a consistently populated record ID in column A. It checks each data row and prints the row number and ID for visible rows. It does not change the worksheet.

Sub ProcessVisibleRowsOnly()
    Dim ws As Worksheet
    Dim dataRange As Range
    Dim cell As Range
    Dim lastRow As Long
    Dim visibleCount As Long

    ' Use a stable key column to find the last data row.
    Set ws = ThisWorkbook.Worksheets("Data")
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    ' Stop if there are no data rows below the header.
    If lastRow < 2 Then Exit Sub

    Set dataRange = ws.Range("A2:A" & lastRow)

    For Each cell In dataRange.Cells
        If Not cell.EntireRow.Hidden Then
            ' Replace this line with the work you want to perform.
            Debug.Print cell.Row, cell.Value
            visibleCount = visibleCount + 1
        End If
    Next cell

    MsgBox visibleCount & " visible data rows processed."
End Sub

To try it, save a copy of the workbook first, then open the Visual Basic Editor from the Developer tab, insert a standard module, and paste in the code. On Windows, Alt+F11 opens the editor; on Mac, use Option+F11 (some keyboards also require Fn). Run the macro from the editor. The sample’s Debug.Print output appears in the Immediate window; replace that statement with your actual task, such as checking a value or copying a visible record. Macros cannot be undone with Ctrl+Z, so a backup matters if your replacement code edits cells.

What counts as a visible row?

Excel’s Hidden property reports whether a row or column is hidden. For a single cell, cell.EntireRow.Hidden is True when its worksheet row is hidden. That includes rows excluded by a filter and rows hidden with Excel’s Hide command. The check therefore follows what is currently displayed rather than trying to infer which filter criteria are active.

This is often exactly what “visible rows” means: include records a person can see, and skip rows they cannot. It also means the code skips a row hidden manually even if it meets the filter criteria. If you need to distinguish manual hiding from filtering, the simple Hidden check does not tell you why the row is hidden. Treat the current visible state as the rule, or redesign the task to work from the filter criteria or a separate flag in the data.

Hidden columns are a different matter. A row can remain visible even when one of its columns is hidden. The example tests row visibility only; it does not omit values from hidden columns unless your processing code explicitly does so.

Choosing the range to loop over

The loop should cover the records, not the header, totals, or unrelated worksheet cells. In the sample, A2:A through the last row is used as a compact way to visit each record once. Column A is assumed to contain an ID on every record. Choose a column that is populated reliably; if that column has gaps, End(xlUp) can identify a row above the true end of your data.

If your data is an Excel Table, using its data body can be clearer than hard-coding row 2. A table’s DataBodyRange represents its data rows, excluding the header and total row. For a table named SalesTable, you can set a range to ws.ListObjects("SalesTable").DataBodyRange.Columns(1) and loop through those cells with the same EntireRow.Hidden condition. Check that the table has data before using DataBodyRange, because it can be Nothing when the table is empty.

For a fixed-size report, a clearly defined range such as ws.Range("A2:A500") can be appropriate. For a changing list, use a table or calculate the last row using a suitable key column. Avoid looping over every row in the worksheet: Excel has over a million rows, and most of them are unlikely to be part of the task.

Filtering, hidden rows, and table behavior

When an AutoFilter hides rows that do not match its criteria, the row-level test in the example skips them. You do not need to remove the filter, show all records, or change the filter selection first. The macro reads the worksheet’s current state, so a user can change the filter and run the same code again to process a different visible subset.

The same approach works with filtered Excel Tables. Table filters hide worksheet rows, and the row-level check sees those rows as hidden. If the table has a totals row, keep it out of the range being processed if it should not count as a record. Using the table’s DataBodyRange helps avoid accidentally including the header or total.

Subtotals and outline groups can also hide rows. A row collapsed by an outline is hidden too, so this method skips it. That can be useful when the goal is to act only on what is displayed, but it may be wrong if you intend to process every underlying record regardless of grouping. Decide whether “visible” means the current screen state or merely “not filtered out” before choosing a method.

Using SpecialCells for visible cells

Another common VBA technique is Range.SpecialCells(xlCellTypeVisible). It returns the visible cells within a range and can be convenient when you want to copy or inspect visible cell values. For example, it is often used after filtering to select visible results without visiting each hidden row.

However, visible cells are not necessarily returned as one continuous block. A filtered list can contain many separate areas. A loop over the returned cells visits cells, not automatically one item per record; if the selected range spans five columns, each visible row contributes five cells. For row-oriented processing, choose a single column as the source range or deliberately handle each area and row.

There is also an important edge case: SpecialCells can raise a run-time error when no matching visible cells are available in the requested range. A range that includes a visible header may still return that header, which is one reason to define the range carefully. If you use this technique, account for the no-results case with narrowly scoped error handling rather than ignoring all errors throughout the macro. For straightforward row processing, testing EntireRow.Hidden is often easier to read and troubleshoot.

Processing the whole visible record

The sample uses a cell in column A as a convenient marker for each row. Once a cell passes the visibility test, its Row property gives the worksheet row number. You can then read or write other columns on that same row, for example by referring to ws.Cells(cell.Row, "B"). This keeps the loop anchored to one record at a time rather than treating every field as a separate record.

For example, a task might read a visible row’s status in column B and place a result in column C. Keep the visibility test around the entire row’s work, so no write or calculation happens for a filtered-out record. If your macro sorts, deletes, or changes the filter while looping, the set and order of rows may change during the run; it is usually simpler to finish the pass before changing the displayed data.

For large datasets, a cell-by-cell VBA loop can be slower than working with arrays or Excel’s built-in features. First make the visible-row logic correct and test it on a copy. If performance becomes a problem, measure the macro and consider loading values into an array or using a table operation, while still ensuring hidden records are excluded as intended.

Common mistakes and checks

Including the header: Start at the first data row, or use a table’s data body, so the header is not processed as a record.
Using an unreliable last-row column: Pick a key column that is filled on every record, or use a defined table range.
Assuming a filter means every row is visible: A filter can leave no matching records, and manual or outline hiding can also affect the result.
Looping across several columns by accident: A range with multiple columns visits each visible cell. Use one marker cell per row when your task is record-based.
Expecting hidden columns to be skipped: EntireRow.Hidden checks the row, not individual column visibility.
Forgetting that changes persist: If you replace the sample’s print statement with edits or deletions, save a backup first; VBA actions are not reversed by Ctrl+Z.

To verify the logic, apply a filter that hides some records, hide one additional row manually, and run the print-only sample. It should list records that remain displayed and omit both kinds of hidden rows. Clear the filter and unhide the manual row afterward if you want to restore the original view.

Frequently Asked Questions

Does this work with AutoFilter? Yes. AutoFilter hides rows that do not match, and the EntireRow.Hidden check skips those rows. The macro uses the filter as it currently appears on the worksheet.

Does it also skip rows I hid manually? Yes. A manually hidden row also has EntireRow.Hidden set to True. The check does not distinguish manual hiding from filtering or outline grouping.

How do I loop through visible rows in a table? Use the table’s DataBodyRange to target its records, then loop over one column and test each cell’s EntireRow.Hidden property. Check that the table is not empty before using its data body.

Will SpecialCells(xlCellTypeVisible) work instead? It can, especially for selecting or copying visible cells, but its result may contain multiple areas and it can error when no visible cells are found. For one action per row, a row-by-row hidden check is often simpler.

Can I process visible rows without changing the filter? Yes. Reading the hidden state does not alter the filter. The sample leaves the current worksheet view as it is.

Which approach should you use?

For most macros that need to perform one action per displayed record, loop over a well-defined data range and process a row only when Not cell.EntireRow.Hidden is true. It handles filtered and manually hidden rows with little setup and makes the intended rule explicit. Use SpecialCells(xlCellTypeVisible) when you specifically need a visible-cell range, such as for a copy operation, and be prepared for multiple areas and an empty result. In either case, use the right data range, test on a copy, and decide whether hidden outline rows should count as invisible for your task.

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.