Iterate through the autofiltered rows

[hheckner]

Hi,

I want to iterate through all rows which are currently visible with an
activated autofilter.
I tried the Range Autofiler.Range but that gave me all rows in (also
the ones which are not visible!)
then i tried: Set oRng = AutoFilter.Range.Cells.SpecialCells
(xlCellTypeVisible) but that gave me only 1 row (I think it will be
the title line).

What I want to do ist

do while ' there are more rows available through the autofilter result
' work on the rows which are visible through the autofilter
loop

I am not interested in the setting filter criterias through my macro!

Any help on this one greatly appreciated

Best regards
Hannes

 

[Mike H]

Hi,

This works on a filtered range in column A and loops through the visible rows

Sub Sonic()
Dim MyRange as Range
lastrow = Cells(Cells.Rows.Count, "A").End(xlUp).Row
Set MyRange = Range("A1:A" & lastrow)
For Each c In MyRange
If Not c.EntireRow.Hidden Then
'your code goes here
End If
Next
End Sub

Mike

 

[Dave Peterson]

Dim VisRng as range
Dim myCell as range

With Worksheets("somesheetnamehere")
With .AutoFilter.Range
If .Columns(1).Cells.SpecialCells(xlCellTypeVisible).Count = 1 Then
'nothing visible
Set VisRng = Nothing
Else
'resize to avoid the header
'and come down one row
'single column of visible cells
Set VisRng = .Resize(.Rows.Count - 1, 1).Offset(1, 0) _
.Cells.SpecialCells(xlCellTypeVisible)
End If
End With
End With

If VisRng Is Nothing Then
MsgBox "No non-blanks in AA column A"
Else
for each mycell in visrng.cells
msgbox mycell.address(0,0)
next mycell
End If