Reversing a lists order in excel for my macro

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!

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

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

Reversing a lists order in excel for my macro

Re: Reversing a lists order in excel for my macro

Re: Reversing a lists order in excel for my macro