Stepping through For Each...Next backwards?

[Mike Lee]

Hello,
Is there a way to use a For Each...Next loop but have VBA
step through the collection from the last element to the
first? I'm trying to step through a range and delete
rows that don't meet certain criteria, and I need to work
from the bottom up. I know I can do it by counting the
rows in the range and using:

for i = lastrow to 1 step -1

But I figured it'd be less lines of code if something
existed like this:

for each cl in rng step backwards

Not a big deal if it doesn't, but figured it was worth
asking.

Thanks to all.

Mike

 

[Chip Pearson]

Mike,

No, you cannot change the order in which a For Each loop
iterates.

 

[Bob Phillips]

Mike,

I don't think so as any range object seems to start at the earlier cell no
matter how you define it.

This code

For Each cell In Range("H10:H1")
MsgBox cell.Address
Next cell

shows H1, H2, etc, not H10, H9, etc.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Mike Lee]

Didn't think so, but figured it was worth asking.

Thanks to both of you for your answers.

Mike

 

[Bob Flanagan]

Step through it one time, assigning the values to an array. Then step
through the array backwards.

Bob Flanagan
Macro Systems
http://www.add-ins.com
Productivity add-ins and downloadable books on VB macros for Excel

 

[Dave Peterson]

Is it a nice single column, single area range?

dim rng as range
dim i as long
set rng = activesheet.range("a1:a10")
for i = rng.cells.count to 1 step -1
msgbox rng(i).address
next i

You could also iterate backwards through areas, rows, columns. It kind of
depends on what you mean by going backwards through a range.