Loop backwards through selection?

[Guest]

Hi,

Can someone please tell me how to loop from the last cell to the first in a
selection?

Thank you

 

[Bob Phillips]

Dim i As Long
Dim j As Long

With Selection
For i = .Rows.Count To 1 Step -1
For j = .Columns.Count To 1 Step -1
Debug.Print .Cells(i, j).Address, .Cells(i, j).Value
Next j
Next i
End With

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Guest]

Hi,

One way,

Loops backwards from A100 to A1 writing the numbers 100 to 1 in each cell

Sub standard()
For x = 100 To 1 Step -1
Cells(x, 1).Value = x
Next
End Sub

Mike

 

[Guest]

Thanks to both for the replies!

Bob, this works nicely; however, it does not work if the selection is
non-contiguous. If there are non-adjacent cells in the selection (I'm
selecting several rows, some of which are not adjacent to other rows in the
selection), the number of rows returned is smaller than the number of rows in
the selection! Do you know of any way around this problem? Thanks again.

 

[Chip Pearson]

For multiple areas (non-contiguous selections) you can adapt Bob's code by
enclosing it in a loop through the Areas of the Selection.

Sub AAA()
Dim Area As Range
Dim AreaNdx As Long
Dim RowNdx As Long
Dim ColNdx
Dim N As Long
For AreaNdx = Selection.Areas.Count To 1 Step -1
Set Area = Selection.Areas(AreaNdx)
With Area
For RowNdx = .Cells(.Cells.Count).Row To .Cells(1, 1).Row
Step -1
For ColNdx = .Cells(.Cells.Count).Column To .Cells(1,
1).Column Step -1
N = N + 1
Cells(RowNdx, ColNdx) = N
Next ColNdx
Next RowNdx
End With
Next AreaNdx
End Sub


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting
www.cpearson.com
(email on the web site)

 

[Guest]

Thank you. I think this issue is resolved now.