Sorting Rows

S

Steve F

I want to sort multiple rows, but seem to only be able to do one at a
time. I know that to sort multiple columns, I can highlight as many
columns as I want and then select A to Z on the Toolbar. But this
doesn't seem to be the case for sorting rows. In terms of sorting rows,
the only thing I can seem to do is to go into
Data-->Sort-->Options-->Select Left to Right. I can then sort a single
row, and from this window it looks like I may--although I haven't
figured out how to get it to work--be able to sort three rows, but I
cannot tell how I might be able to sort more than three rows. For
instance, suppose I have the following data

3, 2, 1
6, 5, 4
98, 97, 96
33, 32, 21

I want to be able to select all of these rows and quickly sort them as
follows:

1, 2, 3
4, 5, 6
96, 97, 98
21, 32, 33

Thank you
 
G

Gord Dibben

Steve

Here is a Tom Ogilvy macro that sorts multiple rows.

Sub SortRows()
Dim r As Long
Dim lrow As Long

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
lrow = ActiveSheet.Cells(Rows.Count, "B").End(xlUp).Row

'Make the r = 3 or whatever the first row of data you want to sort on is.
'The Cells(r, 2) means your data starts in Col 2 or Col B - adjust as
'necessary
'The resize(1, 4) expands the range to 1 cell deep by 4 cells wide

For r = 3 To lrow
With Cells(r, 2).Resize(1, 4)
.Sort Key1:=Cells(r, 1), 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
 
D

David Biddulph

Steve F said:
I want to sort multiple rows, but seem to only be able to do one at a
time. I know that to sort multiple columns, I can highlight as many
columns as I want and then select A to Z on the Toolbar. But this
doesn't seem to be the case for sorting rows. In terms of sorting rows,
the only thing I can seem to do is to go into
Data-->Sort-->Options-->Select Left to Right. I can then sort a single
row, and from this window it looks like I may--although I haven't
figured out how to get it to work--be able to sort three rows, but I
cannot tell how I might be able to sort more than three rows. For
instance, suppose I have the following data

3, 2, 1
6, 5, 4
98, 97, 96
33, 32, 21

I want to be able to select all of these rows and quickly sort them as
follows:

1, 2, 3
4, 5, 6
96, 97, 98
21, 32, 33
Click to expand...

I don't understand your question. Surely you can effectively do the same for
rows as you can do for columns. You can select as many rows (or columns) as
you like, and then sort by any one (or up to 3) rows (or columns) at a time
(just click OK when you've chosen your Data/ Sort/ Options).

In your example, it doesn't matter which row you choose to sort by, you'll
get the result you request. If the values were in a different order in the
different rows, then you'd have to decide on the row to sort by, and if
there were non-unique values in the row by which you sort first, then you
need to choose what to sort by second & third.

Remember that whether you're choosing rows or columns, if you've selected a
number of them together before the sort, it will not sort each one
individually, but will sort keep rows together when you're sorting by
columns, or columns together when you're sorting by rows.
 
G

Gord Dibben

Talk about overkill<g>

Misunderstood what you stated.

As David points out, you should be able to sort multiple rows.

Gord

Steve

Here is a Tom Ogilvy macro that sorts multiple rows.

Sub SortRows()
Dim r As Long
Dim lrow As Long

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
lrow = ActiveSheet.Cells(Rows.Count, "B").End(xlUp).Row

'Make the r = 3 or whatever the first row of data you want to sort on is.
'The Cells(r, 2) means your data starts in Col 2 or Col B - adjust as
'necessary
'The resize(1, 4) expands the range to 1 cell deep by 4 cells wide

For r = 3 To lrow
With Cells(r, 2).Resize(1, 4)
.Sort Key1:=Cells(r, 1), 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
Click to expand...

Gord Dibben MS Excel MVP
 
A

Anthony D

Hi Steve,

The sort function will sort the full data range selected (e.g. in ascending
order) even when only one row (e.g. the first in the range, or any in the
range) or column are specified as "Sort by".

The extra options - the two additional sort criteria Then by - are provided
in case multiple sort criteria are required within the data (e.g. by name
then by telephone number).

Hope that is helpful. Imho, the dialog box with the sort function should
explain this.

Anthony
 
A

Anthony D

ps. I should have noted, as David has, that the actual sort order could be
very different over many different rows depending on the nature of the data,
in which case sorting would be required on a row by row basis or with a macro
as Gord suggests.

A
 
Top