Excel VBA Loop Through Filtered Table Rows

VBA & macros · Updated November 22, 2026

To loop through only the visible rows of a filtered Excel table in VBA, use the SpecialCells(xlCellTypeVisible) method on the table’s DataBodyRange and iterate over the resulting Areas. For example, For Each r In tbl.DataBodyRange.SpecialCells(xlCellTypeVisible).Areas then loop through each cell in each area. This skips hidden rows automatically, unlike a plain For Each over the entire range.

Why a Simple Loop Doesn’t Work

When you filter a table, rows are hidden, not deleted. A standard VBA loop like For Each cell In tbl.DataBodyRange still visits every row, including hidden ones. That means your code processes data the user can’t see, which often produces wrong results or slows down the macro. The SpecialCells(xlCellTypeVisible) trick tells Excel to return only the cells that are currently visible on screen, respecting the filter and any manually hidden rows.

The Core Method: SpecialCells(xlCellTypeVisible)

The key is to first get the table’s data range, then apply SpecialCells. Here’s a complete, working macro that loops through visible rows and writes the value from column A of each visible row to column C:

Sub LoopVisibleRows()
    Dim tbl As ListObject
    Dim rng As Range
    Dim area As Range
    Dim r As Range
    Dim ws As Worksheet
    
    ' Save a copy of your workbook before running any macro
    Set ws = ActiveSheet
    Set tbl = ws.ListObjects(1)  ' Assumes the first table on the sheet
    
    ' Get only the visible cells in the table's data body
    On Error Resume Next
    Set rng = tbl.DataBodyRange.SpecialCells(xlCellTypeVisible)
    On Error GoTo 0
    
    ' If no visible cells (e.g., all rows filtered out), exit
    If rng Is Nothing Then Exit Sub
    
    ' Loop through each contiguous block of visible cells
    For Each area In rng.Areas
        For Each r In area.Rows
            ' r is a single row range; get column A value
            r.Cells(1, 1).Offset(0, 2).Value = r.Cells(1, 1).Value
        Next r
    Next area
End Sub

Notice the On Error Resume Next – if no cells are visible, SpecialCells raises an error, and we handle it gracefully.

How to Handle Multiple Columns and Areas

When you filter a table, visible rows are often not contiguous. Excel returns them as separate Areas in the SpecialCells range. That’s why the outer loop goes through each area. Inside each area, you can loop through rows or cells. To read a specific column, use r.Cells(1, columnIndex) where columnIndex is relative to the table’s data body (1 = first column, 2 = second, etc.). For example, to get the value in the third column of each visible row, use r.Cells(1, 3).Value.

Using AutoFilter.Range Instead

An alternative approach is to use the AutoFilter object directly. If your data is a range (not a formal table), you can use Range.AutoFilter and then loop through Range.SpecialCells(xlCellTypeVisible) the same way. For a table, the ListObject has an AutoFilter property, but the DataBodyRange method is simpler. Here’s a comparison:

  • Table (ListObject): Use tbl.DataBodyRange.SpecialCells(xlCellTypeVisible) – this automatically respects the table’s filter.
  • Regular range: After applying Range.AutoFilter, use Range.SpecialCells(xlCellTypeVisible) on the entire range, then exclude the header row if needed.

Common Pitfalls and How to Avoid Them

One frequent mistake is forgetting that SpecialCells can return multiple areas, so a simple For Each cell In rng might work but can be slower and might cause issues if you try to use Union or Intersect later. Another pitfall is that SpecialCells(xlCellTypeVisible) also includes cells that are hidden because of grouping or manual row hiding, not just filters. If you only want rows hidden by an AutoFilter, you need to check the RowHeight property or use a different method. Also, if the table has no visible rows, SpecialCells returns Nothing – always test for that.

Performance Tips for Large Tables

Looping cell by cell in VBA is slow. For large filtered tables, consider these optimizations:

  • Turn off screen updating and automatic calculations at the start: Application.ScreenUpdating = False and Application.Calculation = xlCalculationManual.
  • Read the entire visible range into a variant array at once, process it in memory, then write back. This is much faster than looping through individual cells.
  • If you only need to perform an operation on each visible row (like delete or copy), use the Areas collection to work with whole blocks at once.

Frequently Asked Questions

Q: How do I loop through filtered rows and copy only visible cells to another sheet?
A: Use rng.Copy Destination:=Sheets("Sheet2").Range("A1") where rng is the SpecialCells(xlCellTypeVisible) range. This copies only visible cells, but note that if there are hidden columns, they are also skipped. To copy only visible rows but all columns, you may need to copy the entire table and then use AutoFilter on the destination.

Q: Why does my loop include hidden rows when I use For Each on the table?
A: Because For Each iterates over all cells in the range, regardless of visibility. You must explicitly use SpecialCells(xlCellTypeVisible) to get only visible cells.

Q: Can I loop through only filtered rows in a table without using SpecialCells?
A: Yes, you can loop through each row and check the Hidden property of the row: If Not tbl.DataBodyRange.Rows(i).Hidden Then. This is slower but gives you more control, especially if you need to handle rows hidden by grouping.

Q: How do I loop through visible rows in a filtered table and delete them?
A: Be careful – deleting rows while looping can skip rows. The safest way is to collect the rows you want to delete in a Range variable using Union, then delete them all at once after the loop. Alternatively, loop through the Areas and delete each area’s rows from bottom to top.

Q: Does SpecialCells(xlCellTypeVisible) work on a filtered table in Excel for Mac?
A: Yes, the method works the same on Mac. The VBA syntax is identical. The only difference is that shortcuts like Ctrl+G (Go To) become Cmd+G on Mac, but the code itself is unchanged.

Q: What if my table has no visible rows after filtering?
A: SpecialCells(xlCellTypeVisible) will return Nothing, and your code must handle that with an If rng Is Nothing Then Exit Sub check. Otherwise, you’ll get a runtime error 1004.

Alternatives and When to Use Them

If you need to loop through rows that meet a condition without actually filtering the table, consider using AutoFilter with criteria and then loop through visible cells. Alternatively, you can use a For Each loop with an If statement checking cell values – this is simpler but slower. For complex criteria, you might use AdvancedFilter to copy matching rows to a new location and then loop through that range. Each method has trade-offs in speed and complexity; choose based on your specific needs.

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.