Sorting Rows
 

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

Sorting Rows
 

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

On Thu, 5 Oct 2006 20:18:55 +0100, Steve F
wrote:


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

Sorting Rows
 

"Steve F" wrote in message
...

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

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.
--
David Biddulph

Sorting Rows
 

Talk about overkill<g

Misunderstood what you stated.

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

Gord

On Thu, 05 Oct 2006 17:53:43 -0700, Gord Dibben <gorddibbATshawDOTca wrote:

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

On Thu, 5 Oct 2006 20:18:55 +0100, Steve F
wrote:


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

Gord Dibben MS Excel MVP

Sorting Rows
 

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


"Steve F" wrote:


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




--
Steve F

Sorting Rows
 

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

"Anthony D" wrote:

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


"Steve F" wrote:


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




--
Steve F