Hi

Does anyone know how to sort a set of cell references (not the values
in the cells, but the cell references - A1, B5...)

I am writing a macro where the user can select any number of cells in
any order, but when I process the macro, I need to start with the
upper left most cell in the selection.

Any help would be greatly appreciated!
Thanks
Tim

Hi,

I started by thinking this was simple and had never recognised that Excel
remembered a selection of cells in the order it was selected. there has got
to be a simpler way but here goes. This uses columns A,B & C on the active
sheet for the sort but parctically you would have to put that out of the way
somewhere. But to test it avoid using those columns in your selection. Right
click the sheet tab, view code and paste this in. make you random selection
of cells and run it

Sub stantial()
Dim bigrange As Range, myrange As Range
x = 1
Set myrange = Selection
For Each c In myrange
Cells(x, 1).Value = c.Address
Cells(x, 2).Value = c.Row
Cells(x, 3).Value = c.Column
x = x + 1
Next
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
Set myrange = Range("A1:A" & lastrow).Resize(, 3)
myrange.Select
Selection.Sort Key1:=Range("B1"), Order1:=xlAscending, Key2:=Range("C1"),
Order2:=xlAscending
Set myrange = Range("A2:A" & lastrow)
Set bigrange = Range(Range("A1").Text)
For Each c In myrange
Set bigrange = Union(bigrange, Range(c.Text))
Next
'this is for debugging only
'to demonstrate cells are in order
For Each c In bigrange
MsgBox c.Address
Next
End Sub

Mike

"Tim879" wrote:

Hi

Does anyone know how to sort a set of cell references (not the values
in the cells, but the cell references - A1, B5...)

I am writing a macro where the user can select any number of cells in
any order, but when I process the macro, I need to start with the
upper left most cell in the selection.

Any help would be greatly appreciated!
Thanks
Tim

On Jun 12, 5:54 am, Tim879 wrote:
Hi

Does anyone know how to sort a set of cell references (not the values
in the cells, but the cell references - A1, B5...)

I am writing a macro where the user can select any number of cells in
any order, but when I process the macro, I need to start with the
upper left most cell in the selection.

Any help would be greatly appreciated!
Thanks
Tim

If your range of cells from which the selection is made is fixed (I've
restricted it to the 100 cells in A1:J10), then maybe this will be
useful...

Public Sub OrderSelectedCells()
Dim rgCell As Range, I As Long
For Each rgCell In Range("A1:J10")
If Not Intersect(Selection, rgCell) Is Nothing Then
Debug.Print rgCell.Address(False, False)
End If
Next
End Sub

It prints a cell's address to the Immediate window if it was selected.
The sequence of the testing for selection, and hence printing order,
is top row to bottom row and left to right.

Perhaps you could substitute Range("A1:J10") with UsedRange.

Ken Johnson

thank you both for your help. I'll play around with the code you've
provided and post what I come up with.


Jun 11, 8:14 pm, Ken Johnson wrote:
On Jun 12, 5:54 am, Tim879 wrote:

Hi

Does anyone know how to sort a set of cell references (not the values
in the cells, but the cell references - A1, B5...)

I am writing a macro where the user can select any number of cells in
any order, but when I process the macro, I need to start with the
upper left most cell in the selection.

Any help would be greatly appreciated!
Thanks
Tim

If your range of cells from which the selection is made is fixed (I've
restricted it to the 100 cells in A1:J10), then maybe this will be
useful...

Public Sub OrderSelectedCells()
Dim rgCell As Range, I As Long
For Each rgCell In Range("A1:J10")
If Not Intersect(Selection, rgCell) Is Nothing Then
Debug.Print rgCell.Address(False, False)
End If
Next
End Sub

It prints a cell's address to the Immediate window if it was selected.
The sequence of the testing for selection, and hence printing order,
is top row to bottom row and left to right.

Perhaps you could substitute Range("A1:J10") with UsedRange.

Ken Johnson