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

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

"hheckner" wrote:

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

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

hheckner wrote:

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

--

Dave Peterson