Iterating through a range backwards

[bill]

Using a FOR EACH, can you iterate through a range backwards?

I know I can do it using:
FOR THISROW = BOTTOMROW TO TOPROW STEP - 1
but that is so blunt!

 

[al_b_cnu]

Hi,

This isnt much use, but satisfies your criteria:

Sub xxx()
Dim R As Range

For Each R In Range("A8,A7,A6,A5,A4,A3,A2,A1")
    MsgBox R.Address
Next R
End Sub

HTH

Alan

 

[al_b_cnu]

How about

Sub xxx()
Dim R As Range
Dim rngA As Range

Set rngA = Range("A2:A11")

For Each R In rngA
    MsgBox Cells((rngA.Rows.Count + rngA.Row + 1) - R.Row, 1).Address
Next R
End Sub

Alan

 

[Legacy 21301]

How about

Sub xxx()
Dim R As Range
Dim rngA As Range

Set rngA = Range("A2:A11")

For Each R In rngA
    MsgBox Cells((rngA.Rows.Count + rngA.Row + 1) - R.Row, 1).Address
Next R
End Sub

Alan

Shouldn't that be :

MsgBox Cells(rngA.Rows.Count + 2 * rngA.Row - 1 - R.Row, 1).Address

 

[al_b_cnu]

Erm... oh yeah.

Thanks Ponsy Nob.

Alan

 

[bill]

This isnt much use, but satisfies your criteria:

For Each R In Range("A8,A7,A6,A5,A4,A3,A2,A1")
    MsgBox R.Address
Next R

Perhaps one could argue that my criteria is satisifed but as far as I can see, the range you've defined is still being processed from the 1st to last element.

No matter however as I appreciate your response.

 

[phantom1975]

Any time you are going to remove/add/insert rows/columns, I suggest going backward for that very reason. It sounds like you are just trying to sharpen your tools and know the limits of your options. I'm afraid that For...Each is limited to going forward.

You know, with a little bit of mustard, eating my words isn't so bad. I completely forgot.....you really can do just about anything with VB. It just depends on how willing you are to put something together.