Sorting

[Gene DeLallo]

I have 5 columns of names that I want to sort into alpa order. How can
I sort the names across columns without having to move all the names
into column A?

 

[J.E. McGimpsey]

I have 5 columns of names that I want to sort into alpa order. How can
I sort the names across columns without having to move all the names
into column A?

How is your data laid out now?

When you choose Data/Sort you can select the column you wish to sort
on.

If you make sure your entire data set is selected, all the rows will
be sorted. Alternatively, if you select only one cell in your data,
then choose Data/Sort, all of the rows that contain data contiguous
with that cell will sort.

 

[Bernard Rey]

J.E. McGimpsey wrote :

How is your data laid out now?

When you choose Data/Sort you can select the column you wish to sort
on.

I think Gene has data in different rows and ha wants to sort them all. Not
only on one specific column.

I'm not sure if there's an easier way (and I'm sure there must be some
"cleaner" ways) but I can do this with a macro. It copies the data from the
selected area in a new sheet, sorts it and then copies it back where it was.


Sub SortIt()

Application.ScreenUpdating = False

MyLastColumn = Selection.Columns.Count
MyLastRow = Selection.Rows.Count
MyFirstColumn = Selection.Columns(1).Column
MyFirstRow = Selection.Rows(1).Row

CurrentSheetName = ActiveSheet.Name
ActiveWorkbook.Worksheets.Add
ActiveSheet.Name = "NewSheet"

For i = 1 To MyLastColumn
Sheets(CurrentSheetName).Activate
Selection.Range(Cells(1, i), Cells(MyLastRow, i)).Copy
Sheets("NewSheet").Cells(1 + ((i - 1) * MyLastRow), 1).PasteSpecial
Next i

Sheets("NewSheet").Activate
Range("A1:A" & MyLastRow * MyLastColumn).Sort Key1:=Range("A1"), _
Order1:=xlAscending

For j = 1 To MyLastColumn
Sheets("NewSheet").Activate
Range(Cells(1 + ((j - 1) * MyLastRow), 1), _
Cells(MyLastRow + ((j - 1) * MyLastRow), 1)).Copy
Sheets(CurrentSheetName).Activate
Cells(MyFirstRow, MyFirstColumn - 1 + j).PasteSpecial
Next j
Application.DisplayAlerts = False
Sheets("NewSheet").Delete
Application.DisplayAlerts = True
Application.ScreenUpdating = True

End Sub

 

[J.E. McGimpsey]

I think Gene has data in different rows and ha wants to sort them all. Not
only on one specific column.

I'm not sure if there's an easier way (and I'm sure there must be some
"cleaner" ways) but I can do this with a macro. It copies the data from the
selected area in a new sheet, sorts it and then copies it back where it was.

On re-reading, you're undoubtedly right. Should have been obvious
the first time...