Submit My Question - Recent Questions - Recent Answer
Msdn Tech >> Microsoft ISV Community Center Forums >> Visual Basic for Applications (VBA)

Reversing a lists order in excel for my macro

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!
[839 byte] By [soma34] at [2007-12-27]
«« Possiable to have text with no background or transparent background
»» Transformation from 10 views oracle to sql server
# 1

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

ADG at 2007-9-3 > top of Msdn Tech,Microsoft ISV Community Center Forums,Visual Basic for Applications (VBA)...
# 2
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

AndyPope at 2007-9-3 > top of Msdn Tech,Microsoft ISV Community Center Forums,Visual Basic for Applications (VBA)...
Microsoft ISV Community Center Forums
Visual Basic for Applications (VBA)

Microsoft ISV Community Center Forums Hot Topic

Microsoft ISV Community Center Forums New Topic

Microsoft ISV Community Center Forums

Java / MSDN / Linux Tech / PHP Tech / MSDN DotNet / MSDN TECH / Health Library / Programmers Tech / visual basic Tech / Developer Tech / Ipod faqs / Msdn China Tech

Site Classified