Easiest way to loop through a filtered list with VBA?
I would recommend using Offset
assuming that the Headers are in Row 1. See this example
Option Explicit
Sub Sample()
Dim rRange As Range, filRange As Range, Rng as Range
'Remove any filters
ActiveSheet.AutoFilterMode = False
'~~> Set your range
Set rRange = Sheets("Sheet1").Range("A1:E10")
With rRange
'~~> Set your criteria and filter
.AutoFilter Field:=1, Criteria1:="=1"
'~~> Filter, offset(to exclude headers)
Set filRange = .Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow
Debug.Print filRange.Address
For Each Rng In filRange
'~~> Your Code
Next
End With
'Remove any filters
ActiveSheet.AutoFilterMode = False
End Sub
Suppose I have numbers 1 to 10 in cells A2:A11
with my autofilter in A1
. I now filter to only show numbers greater than 5 (i.e. 6, 7, 8, 9, 10).
This code will only print visible cells:
Sub SpecialLoop()
Dim cl As Range, rng As Range
Set rng = Range("A2:A11")
For Each cl In rng
If cl.EntireRow.Hidden = False Then //Use Hidden property to check if filtered or not
Debug.Print cl
End If
Next
End Sub
Perhaps there is a better way with SpecialCells
but the above worked for me in Excel 2003.
EDIT
Just found a better way with SpecialCells
:
Sub SpecialLoop()
Dim cl As Range, rng As Range
Set rng = Range("A2:A11")
For Each cl In rng.SpecialCells(xlCellTypeVisible)
Debug.Print cl
Next cl
End Sub