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, useRange.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 = FalseandApplication.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
Areascollection 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
- Excel Template for Tracking Monthly Expenses
- Excel Template for Timesheet Job Tracking
- Excel Template for Saving Goal Tracking
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Excel VBA Macro Not Running? Fixes That WorkIf your Excel VBA macro is not running, the most common causes are macros being disabled by security…
- Create a Toggle Button in Excel VBA: Setup and CodeTo create a toggle button in Excel VBA on Windows, choose Developer > Insert > Toggle Button under…
- Excel VBA Automation Error: Causes, Fixes, and Checks“Excel VBA Automation error: Exception occurred” is a broad error from an object that VBA is controlling, often…
- VBA Active Worksheet: How to Reference the Current SheetIn Excel VBA, use ActiveSheet to refer to the sheet that is currently active in the Excel window.…
- Macro Recorder App: What It Does and How to ChooseA macro recorder app captures a sequence of actions so you can repeat them automatically; in desktop Excel…
- Excel VBA Format Cell Color: Fill, Font, and RulesTo format a cell’s fill color with Excel VBA, set its Interior.Color property; for example, Range("A1").Interior.Color = RGB(255,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.