Need to alphabetize data from separate cells within same row
Sub SortRows()
'Tom Ogilvy macro
Dim r As Long
Dim lRow As Long
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
lRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
'Make the r = 1 whatever the first row of data you want to sort on is.
'The Cells(r, 1) means your data starts in Col 1 or Col A - adjust as
necessary
'The resize(1, 7) expands the range to 1 cell deep by 7 cells wide
For r = 1 To lRow
With Cells(r, 1).Resize(1, 7)
.Sort Key1:=Cells(r, 2), Order1:=xlAscending, Header:=xlGuess, _
Orientation:=xlLeftToRight, DataOption1:=xlSortNormal
End With
Next r
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub
Gord Dibben MS Excel MVP
On Mon, 30 Nov 2009 13:26:02 -0800, marketingchickie
wrote:
Hi there,
I have a worksheet with 6,500 rows of data. In each row, there are up to 6
cells containing a name in "Last, First" format. I need to alphabetize the
data from these 6 cells so they go across the row in order left to right,
alpha by last name.
Example of my data layout:
Column 1 Column 2 Column 3
Row 1 Jackson, Bob Anderson, Lisa Cardinal, Mike
Row 2 Tomlinson, Steve Dodge, Sarah White, Brian
I need them to be in this order:
Column 1 Column 2 Column 3
Row 1 Anderson, Lisa Cardinal, Mike Jackson, Bob
Row 2 Dodge, Sarah Tomlinson, Steve White, Brian
Is there a formula I can apply that will pull from only the portion of the
data before the comma in each cell? Or do I need to separate out all of the
names so the first and last names are in separate columns for this to work?
Please help! Thank you.
|