To loop through only the visible rows in a filtered Excel range with VBA, use the SpecialCells(xlCellTypeVisible) method to get a reference to the filtered cells, then iterate over that range with a For Each loop. This is the fastest and most reliable approach because it skips hidden rows automatically, unlike a plain For loop that would process every row in the range.
Why a Normal Loop Fails on Filtered Data
When you apply an AutoFilter, Excel hides rows that don’t match the criteria. A standard For loop that goes from the first row to the last row of the data range still sees those hidden rows—they aren’t deleted, just hidden. So if you try to sum values or copy data with a simple loop, you’ll include rows that the user can’t see, producing wrong results.
For example, suppose you have data in A1:A10 and you filter to show only rows 2, 5, and 8. A loop like For i = 1 To 10 will process rows 1, 3, 4, 6, 7, 9, and 10 as well, even though they are hidden. The SpecialCells approach avoids this entirely.
Method 1: Using SpecialCells(xlCellTypeVisible)
The most direct way is to get the visible cells in the used range (or a specific column) and loop through them. Here’s a complete example that writes the value from column A of each visible row to column C:
Sub LoopVisibleRows()
Dim ws As Worksheet
Dim rngVisible As Range
Dim cell As Range
' Save a copy of your workbook before running any macro
Set ws = ThisWorkbook.Sheets("Sheet1")
' Get only the visible cells in the used range
On Error Resume Next
Set rngVisible = ws.UsedRange.SpecialCells(xlCellTypeVisible)
On Error GoTo 0
' If no visible cells (e.g., all rows hidden), exit
If rngVisible Is Nothing Then Exit Sub
' Loop through each visible cell in the first column
For Each cell In rngVisible.Columns(1).Cells
' Skip the header row if it's included
If cell.Row > 1 Then
cell.Offset(0, 2).Value = cell.Value
End If
Next cell
End Sub
This works because SpecialCells(xlCellTypeVisible) returns a range that contains only the cells that are not hidden. Note that if the filter hides all rows, the method returns Nothing, so you need error handling (as shown) to avoid a runtime error.
Method 2: Using AutoFilter.Range and Offset
Another approach is to use the AutoFilter.Range property to get the entire filtered range, then loop through the rows that are visible by checking the Hidden property of each row. This gives you more control if you need to process entire rows rather than individual cells.
Sub LoopVisibleRowsByRow()
Dim ws As Worksheet
Dim rng As Range
Dim i As Long
Set ws = ThisWorkbook.Sheets("Sheet1")
' Assume filter is already applied on Sheet1
If ws.AutoFilter Is Nothing Then Exit Sub
Set rng = ws.AutoFilter.Range
' Loop from row 2 to the last row in the filter range
For i = 2 To rng.Rows.Count
' Check if the row is hidden (if not hidden, it's visible)
If ws.Rows(rng.Row + i - 1).Hidden = False Then
' Process the row - for example, put a marker in column D
ws.Cells(rng.Row + i - 1, 4).Value = "Visible"
End If
Next i
End Sub
This method is a bit slower because it checks every row, but it’s useful when you need to know the row number or when you want to perform operations that affect the entire row.
How to Choose the Right Approach
- Use
SpecialCellswhen you only need to work with specific columns or cells in the visible rows—it’s faster and simpler. - Use the row-by-row check when you need to reference the row number or when you need to process multiple columns in a coordinated way.
- If you need to copy visible rows to another sheet, use
SpecialCells(xlCellTypeVisible).Copyand paste—it’s the most efficient way. - If the filter is on a ListObject (Excel Table), use
ListObject.Range.SpecialCells(xlCellTypeVisible)instead ofUsedRange.
Common Problems and Fixes
Problem: The loop includes the header row. If your data has a header, the SpecialCells range will include the header cell if it’s visible. You can skip it by checking cell.Row > 1 or by using rngVisible.Offset(1, 0) if the header is in row 1.
Problem: The code errors when all rows are hidden. As mentioned, SpecialCells returns Nothing. Always use On Error Resume Next and check for Nothing.
Problem: The loop processes cells outside the data range. UsedRange can include empty cells that were once formatted. To avoid this, use a specific range like ws.Range("A1:A100") or use CurrentRegion if your data is contiguous.
Problem: The filter is applied to a table, not a range. In that case, use ws.ListObjects("Table1").Range.SpecialCells(xlCellTypeVisible).
Performance Tips for Large Data Sets
If you’re looping through thousands of rows, the SpecialCells method is significantly faster because it only iterates over visible cells. To further speed up your code, turn off screen updating and automatic calculations at the start of your macro:
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
' ... your loop ...
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Remember to reset these settings even if an error occurs—use On Error GoTo to ensure they’re turned back on.
Alternatives to Looping
In many cases, you don’t need a loop at all. Excel has built-in features that work on filtered data directly:
- Copy visible cells: Select the range, press Alt+; (Go To Special > Visible cells only), then copy and paste.
- Use SUBTOTAL function:
=SUBTOTAL(9, A2:A100)sums only visible cells in a filtered range. - Use AGGREGATE:
=AGGREGATE(9, 5, A2:A100)also ignores hidden rows. - Use Power Query: Load the data, filter, and transform without VBA.
These are often faster and easier to maintain than a custom VBA loop.
Frequently Asked Questions
Q: How do I loop through only visible rows in a filtered range and copy them to another sheet?
A: Use rngVisible.Copy and paste to the destination. For example: ws.UsedRange.SpecialCells(xlCellTypeVisible).Copy Destination:=Sheets("Output").Range("A1"). This copies all visible cells, including headers.
Q: Why does my loop still process hidden rows even when I use For Each?
A: Because For Each over a normal range (like ws.UsedRange) includes all cells, hidden or not. You must explicitly use SpecialCells(xlCellTypeVisible) to get only visible cells.
Q: Can I loop through filtered rows in an Excel Table (ListObject)?
A: Yes. Use ListObject.Range.SpecialCells(xlCellTypeVisible) to get the visible cells of the table, then loop as usual. Note that the table header is always visible, so skip it if needed.
Q: How do I count the number of visible rows in a filtered range?
A: Use rngVisible.Rows.Count after getting the visible cells. For example: ws.UsedRange.SpecialCells(xlCellTypeVisible).Rows.Count gives the total number of visible cells, but if you have multiple columns, divide by the number of columns to get row count.
Q: What if my filter is on a range that has blank rows?
A: Blank rows are not hidden by filtering—they are just empty. SpecialCells(xlCellTypeVisible) will include them. To skip blank cells, add a check like If cell.Value <> "" Then inside your loop.
Q: Does SpecialCells(xlCellTypeVisible) work if the worksheet is grouped or has outline rows?
A: Yes, it works with manually hidden rows and columns as well. It returns only cells that are not hidden due to any reason (filter, grouping, or manual hide).
Related
- Excel Template for Family Budget: How to Build One
- Excel Template for HOA Budget: How to Build One
- Excel Template for Event Budget: How to Build One
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Excel VBA Dictionary Add Array: Store and Retrieve ValuesYes. In Excel VBA, add an array to a Dictionary by passing it as the item in the…
- Excel VBA: Hide a Column RangeTo hide a range of columns with Excel VBA, set its Hidden property to True: use Columns("B:D").Hidden =…
- VBA Optional Parameters: Syntax and ExamplesYes. In VBA, add the Optional keyword to a procedure parameter and, for most typed parameters, provide a…
- Excel VBA Set Cell Color by Hex CodeTo set an Excel cell’s fill color from a hex value such as #1E90FF, convert its red, green,…
- Excel VBA: Find the Last Row of Data in a Column EasilyTo find the last populated row in column A with Excel VBA, use Cells(Rows.Count, "A").End(xlUp).Row, with the worksheet…
- VBA UserForms: What They Are and How to Use ThemA VBA UserForm is a custom dialog window you build in the Visual Basic for Applications editor and…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.