For...next in reverse

[Guest]

Is it possible to do a for each..next loop through a range in reverse e.g

dim grCell as range

for each grCell in sheets("sheet1").range("A100:A1")
...do something here
next grCell

I've look through the help files but no luck.
Thank you for your time

 

[Guest]

try looping with a descending index (i.e. Step -1)

ex.

for i = sheets("sheet1").range("A100:A1").cells.count to 1 Step -1
do something on sheets("sheet1").range("A100:A1").cells(i)
next

-Todd

www.ManagementAnalytics.com

 

[Myrna Larson]

Not with For Each, but with an "old style" For/Next loop that uses a counter
and the index property:

With Sheets("Sheet1").Range("A1:A100")
For i = .Cells.Count To 1 Step -1
'then one or the other of these constructs:

.Cells(i).ClearContents

'or
Set grCell = .Cells(i)
grCell.ClearContents

Next i
End With

On Tue, 12 Oct 2004 06:19:04 -0700, Rod Jones <Rod

 

[Tom Ogilvy]

not really.

you can do

Dim i as long, grCell as Range
for i = 100 to 1 step - 1
set grCell = Sheets("Sheet1").Cells(i,1)

Next

or you can play games

dim grCell as range
Dim rng as Range, grCell1 as Range
set rng = sheets("sheet1").range("A1:A100")
for each grCell in rng
set grCell1 = rng(101 - grCell.row)
...do something here
next grCell

Reversing A1:A100 doesn't do what you want.