Looping through visible rows only

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

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
nick_hodgeTAKETHISOUT@zen.co.uk.ANDTHIS


"Rasmus" <rasmus@DeLeTeTHISvildere.dk> wrote in message
news:%k_5e.15278$6k4.1217907@news20.bellglobal.com...
>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
>
>