soma34


Hi there. I have a macro that im building within excel, but am stuck on an excel function. Does anyone know how to reverse the order of a list ini excel. I have a column set up of names. How can i select this list and have it reverse the list in a separate column. I dont want it reversed alphabetically, or numerically... my example would be this...

Cell A1 - A5

Paul
Barry
John
Steve
Jim

i would like to either select the 5 cells and have click a button to reverse this order, or have it return the reversed order in column B1 - B5... giving me the result of

Jim
Steve
John
Barry
Paul

The only way ive seen how to do it is with the offset function but ive had alot of trouble with that for some reason and hoped someone had another idea.

Thankyou!




Re: Reversing a lists order in excel for my macro

ADG


Hi

Will the list / selection always be the same size and cell locations If so a simple for next loop can do the job eg

for x=1 to 5

worksheets("Sheet1").cells(6-x,2).value =worksheets("Sheet1").cells(x,1).value

next






Re: Reversing a lists order in excel for my macro

Andy Pope

This takes ADG's code and allows it to work on any single column selection.

Sub ReverseSelection()

Dim x
Dim lngNItems As Long

If TypeOf Selection Is Range Then
With Selection
If .Columns.Count > 1 Then Exit Sub ' too wide a selection
If .Column() = .Parent.Columns.Count Then Exit Sub ' now column to right

lngNItems = .Rows.Count

For x = 1 To lngNItems
.Cells(lngNItems - x + 1, 1).Offset(0, 1).Value = .Cells(x, 1).Value
Next
End With
End If

End Sub