Looping through visible rows only

[Rasmus]

I run through the rows of a sheet using this code:
----------------------------------------------------------------
i = 1
Do
If Not Range("b" & i).EntireRow.Hidden then
' Do commands
End If
i = i + 1
Loop Until range("a" & i) = ""
----------------------------------------------------------------
Isn't there a FASTER way of going to the next VISIBLE row ? - Because if
only 5 rows are visible (using autofilter) in a sheet of 25,000 rows then
the above code is a waste of time and takes too long.

Please note; I would like to AVOID actually ACTIVATING any cells. I was
thinking using this line;
Range("j" & (i)).Offset(1,0)
in combination with the SpecialCells & xlCellTypeVisible commands, but how ?

Please help!

(c:
Rasmus

 

[Nick Hodge]

Rasmus

I would do something like the code below

Sub IterateVisibleRows()
Dim rCell As Range
Dim lLastRow As Long
lLastRow = Range("A65536").End(xlUp).Row
For Each rCell In Range("A1:A" & lLastRow).SpecialCells(xlCellTypeVisible)
rCell.Offset(0, 1).Value = "This is visible"
Next rCell
End Sub

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)