To loop through only visible worksheet rows in Excel VBA, get a range of the data cells and use SpecialCells(xlCellTypeVisible), then process each returned cell or row. This works with rows hidden by AutoFilter and rows hidden manually; exclude the header from the range if you do not want to process it.
The basic approach
Excel does not provide a separate “visible rows only” loop statement. Instead, VBA can ask a range for cells of the visible-cell type. The returned range can consist of several separate areas because filtered-out rows break up the visible data. A For Each loop handles those separate areas without requiring you to calculate where each visible block begins and ends.
For row-based work, use one column in the data body as the range to inspect. Each visible cell in that column identifies one visible row. This avoids visiting the same row multiple times, which could happen if you looped through every cell in a multi-column visible range.
The example below assumes row 1 contains headings, data begins in row 2, column A is part of the data, and column D is where the example writes a status. Change the worksheet, columns, and action to match your workbook.
A macro for visible data rows
Before running a macro, save a copy of the workbook: changes made by VBA cannot be reversed with Ctrl+Z. Save a macro-enabled workbook as an .xlsm file if you need to keep the code.
Option Explicit
Sub MarkVisibleRows()
Dim ws As Worksheet
Dim lastRow As Long
Dim dataRange As Range
Dim visibleCells As Range
Dim cell As Range
Set ws = ThisWorkbook.Worksheets("Data")
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
' Row 1 is the header; there is no data to process above row 2.
If lastRow < 2 Then Exit Sub
Set dataRange = ws.Range("A2:A" & lastRow)
' SpecialCells raises an error when the range has no visible cells.
On Error Resume Next
Set visibleCells = dataRange.SpecialCells(xlCellTypeVisible)
On Error GoTo 0
If visibleCells Is Nothing Then Exit Sub
For Each cell In visibleCells.Cells
' Example action: write a status in column D of that row.
ws.Cells(cell.Row, "D").Value = "Reviewed"
Next cell
End Sub
The worksheet named Data must exist, or change that line to the correct sheet name. The last-row calculation uses column A, so choose a column that is reliably populated through the final data row. If column A has gaps or does not reach the bottom of the data, use a more dependable column or set the range from a table instead.
How the visible-cell loop works
The code first finds the last used row in its chosen column and builds a range from A2 down to that row. Starting at row 2 leaves the header out. It then calls SpecialCells(xlCellTypeVisible) on that range. Excel returns the cells that are visible, even if they are spread across multiple nonadjacent sections.
Inside the loop, cell.Row gives the worksheet row number for the current visible cell. The example writes “Reviewed” to column D on that same row. Replace that action with your own work, such as reading values, copying data, or applying formatting. If you are changing the same column used to identify the visible cells, consider whether that change could affect later steps in your procedure.
The error handling is deliberately narrow. If no cells are visible, SpecialCells can raise an error rather than return a usable range. The code temporarily suppresses that error for the assignment, restores normal error handling immediately afterward, and exits if no range was assigned. Avoid leaving On Error Resume Next active across the processing loop, since it can hide unrelated problems.
Using the macro with AutoFilter
You do not need to create an AutoFilter inside the macro. If the worksheet is already filtered, the visible-cell range reflects that current view. For example, if a list has been filtered to show only orders with a particular status, the loop processes the rows that remain visible and skips rows excluded by the filter.
To apply a filter in Excel for Microsoft 365 on Windows, select the data and use Data > Filter, then use the header drop-down to choose criteria. The macro can then run against the filtered results. Clearing or changing the filter changes which rows are returned on the next run; the macro does not permanently remember a previous set of visible rows.
When working with a filtered list, make sure the range you pass to SpecialCells covers the data rows, not just the heading. A range that includes the header may return it as visible, so a loop over that range could treat the heading like a record. Starting at the first data row is the straightforward way to avoid that mistake.
Working with Excel tables
Excel tables, also called ListObjects in VBA, are often safer than guessing the last row from a worksheet column. A table has a named data body that expands as records are added. You can use a table column as the visible-cell source, while still using the current filter state.
For a table named SalesTable with a column named OrderID, replace the range setup in the example with code that gets that table column’s data body. Check that the table has data first: a table with no data rows has no usable data body range. The relevant statements are:
Dim tbl As ListObject
Set tbl = ws.ListObjects("SalesTable")
If tbl.DataBodyRange Is Nothing Then Exit Sub
Set dataRange = tbl.ListColumns("OrderID").DataBodyRange
After assigning dataRange, use the same visible-cell assignment and loop shown above. The table header is not part of DataBodyRange, so it is excluded naturally. Use a column that exists in the table, and update the table and column names to match the workbook exactly.
Visible rows versus visible cells
xlCellTypeVisible identifies visible cells, not an abstract collection of rows. In the main example, the source range is only one column wide, so there is one candidate cell per data row and each row is processed once. If you instead pass a range covering columns A through D and loop through all returned cells, a visible row can be encountered once for each included column.
To process several fields from a row, use the source cell only to identify the row, then read or write other columns using its row number. For example, inside the loop you could read the value in column B with ws.Cells(cell.Row, "B").Value. This keeps the iteration row-oriented without duplicating work.
There is also a difference between a hidden row and a cell that is not visible because its column is hidden. The example uses column A as its source, so if column A itself is hidden, its cells may not be returned as visible. Choose a source column that is not hidden, or adjust the approach if users may hide that column.
Common problems and how to avoid them
- The header is processed. Start the range at the first data row, or use a table’s
DataBodyRange, rather than including the header. - The macro reports an error when a filter leaves no records. Handle the possible error from
SpecialCellsand test whether the resulting range isNothing, as in the example. - Some final records are skipped. Check how the last row is determined.
End(xlUp)on column A finds the last nonempty cell in that column, not necessarily the last row containing data elsewhere. - A row is handled more than once. Use a one-column source range and then refer to the other fields by row number.
- The result differs after changing filters. The macro reads the worksheet’s current visibility. Apply the intended filter before running it, or have the macro set the filter criteria explicitly.
- Hidden rows are included unexpectedly. Confirm that the range is on the intended worksheet and that the cells used as the source are not in a hidden column. Test with a small sample and inspect the returned row numbers.
The loop does not decide whether a row is “important” or whether it matches a business rule; it follows Excel’s current visibility. If the requirement is to process records matching a condition regardless of what a user has hidden or filtered, test the cell values directly instead of relying on visible status.
Speed, workbook safety, and platform notes
For a few hundred or a few thousand visible rows, a cell-by-cell loop is often easy to understand and maintain. For very large datasets, repeated reads and writes between VBA and worksheet cells can be slow. A common optimization is to read a block of values into a VBA array, work with the values in memory, and write results back in a block. However, preserving only the visible rows while doing bulk operations takes more care, so verify that the chosen method does not overwrite filtered-out records.
Use a copy of the workbook while developing the macro, especially if it writes values or changes formatting. Macro security settings may prevent code from running; only enable macros in files you trust. On Windows, Alt+F11 opens the Visual Basic Editor in many Excel setups. On a Mac, use Option+F11 (sometimes Fn+Option+F11) or open the editor from the Developer tab. Excel for the web can open workbooks containing VBA but cannot run VBA macros; run the code in the desktop Excel application.
Frequently Asked Questions
Does SpecialCells(xlCellTypeVisible) work with filtered rows?
Yes. When you call it on a data range, it returns cells visible under the worksheet’s current filter and hidden-row state. The result can contain multiple separate areas.
Does the loop include manually hidden rows?
No, provided the source cells are in a visible column and the range is correct. Manually hidden rows, as well as rows hidden by a filter, are excluded from the visible-cell result.
How do I skip the header row?
Start the source range on the first data row, such as row 2 when row 1 is the heading. With a table, use its DataBodyRange, which excludes the header.
Why does SpecialCells cause an error when no rows are visible?
Excel may raise an error when there are no qualifying visible cells in the requested range. Use limited error handling around the SpecialCells call, then check whether the resulting range is Nothing before looping.
Can I run a VBA macro 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 run the macro; VBA availability and some workbook features can differ by platform.
Related
- Excel Text to Columns: Split at the Last Space
- Excel Text to Columns: Split at Line Breaks
- Excel Text to Columns Greyed Out? Causes and Fixes
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- VBA Move UserForm Without a Mouse: Keyboard MethodsYes. In Excel VBA, move a UserForm in code by changing its Left and Top properties; to move…
- Excel VBA: Find the Last Row With a Specific ValueTo find the last row containing a particular value, search the target column with VBA’s Range.Find method, set…
- VBA Macro Copy and Paste: Methods, Examples, and SafetyIn Excel VBA, copy and paste cells with the Range.Copy method: specify the source range and a destination,…
- Excel VBA Compile Error: Object Required ExplainedIn Excel VBA, the compile error “Object required” usually means a statement expects an object but is being…
- Excel VBA Sleep: Pause a Macro SafelyTo pause an Excel VBA macro on Windows, declare the Windows API function Sleep in a standard VBA…
- Unload UserForm VBA: Close Forms and Reset State SafelyIn VBA, use Unload UserForm1 to close and unload a form, or use Unload Me from code inside…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.