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

Mike,

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


"Mike Lee" wrote in message
...
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

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" wrote in message
...
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

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

Thanks to both of you for your answers.

Mike


-----Original Message-----
Mike,

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


"Mike Lee" wrote
in message
...
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




.

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

"Mike Lee" wrote in message
...
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

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.



Mike Lee wrote:

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

--

Dave Peterson