Sorting more than 3 columns

[Space Norman]

Sorry for what undoubtedly a dumb question. I have a large spreadsheet that
I'm trying to create a macro that will sort the data (12,000+ rows) using a
sort with 4 keys. The following code gets me 3 columns - but will not
support the addition of a "Key4".

When I do it manually it's a two stage process - first, sort on the entire
spreadsheet on the first 3 columns - next stage is to then manually page
through the spreadsheet and performing a "mini-sort" on columns 2, 3 and 4
whenever I see the value of column 1 has changed.

I'm thinking I'll need to sort on the column 1 - then loop through the
spreadsheet identifying to identify the first row in which a new value
appears in column one and continue on to identify the last row in which that
same value appears. Then execute a sort on a range defined by the first /
last row identified. Unfortunately, I'm brand new to this VB stuff and
simply don't know how to do this.

Can anybody point me in the right direction?

SpaceNorman

********
Sub NewSort()
Range("SortRange").Select
Selection.Sort Key1:=Range("W5"), Order1:=xlAscending, Key2:=Range("X5") _
, Order2:=xlAscending, Key3:=Range("Y5"), Order3:=xlAscending,
Header:=xlGuess _
, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal, DataOption2:=xlSortTextAsNumbers,
DataOption3 _
:=xlSortNormal
Range("A1").Select
End Sub

 

[Lars-Åke Aspelin]

Sorry for what undoubtedly a dumb question. I have a large spreadsheet that
I'm trying to create a macro that will sort the data (12,000+ rows) using a
sort with 4 keys. The following code gets me 3 columns - but will not
support the addition of a "Key4".

When I do it manually it's a two stage process - first, sort on the entire
spreadsheet on the first 3 columns - next stage is to then manually page
through the spreadsheet and performing a "mini-sort" on columns 2, 3 and 4
whenever I see the value of column 1 has changed.

I'm thinking I'll need to sort on the column 1 - then loop through the
spreadsheet identifying to identify the first row in which a new value
appears in column one and continue on to identify the last row in which that
same value appears. Then execute a sort on a range defined by the first /
last row identified. Unfortunately, I'm brand new to this VB stuff and
simply don't know how to do this.

Can anybody point me in the right direction?

SpaceNorman

********
Sub NewSort()
Range("SortRange").Select
Selection.Sort Key1:=Range("W5"), Order1:=xlAscending, Key2:=Range("X5") _
, Order2:=xlAscending, Key3:=Range("Y5"), Order3:=xlAscending,
Header:=xlGuess _
, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal, DataOption2:=xlSortTextAsNumbers,
DataOption3 _
:=xlSortNormal
Range("A1").Select
End Sub

Try this:

First sort on the least significant column.
Then sort on the other three columns.
The result will be what you expect - I guess.

Hope this helps / Lars-Åke

 

[Charlie]

Yes, it's too bad we're limited to three columns, but usually that's
sufficient. What I do is create a "Sorter" column concatenating all four key
columns into it:

(Col-E) = CONCATENATE(A1,B1,C1,D1)

Make sure key columns are in the correct order. Copy down column "E" (or
whatever), and sort on the Sorter column. Hide it if you want.

 

[Fleone]

I am not sure what version of Excel you are using, but this seemed to work
for me using Excel 2007. This was a short sort range of 12 rows, but you
should be able to change the ending range to encompass what you are looking
for.

Sub sorttest()
'
' sorttest Macro
'

'
Columns("A:E").Select
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add
Key:=Range("A2:A12") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add
Key:=Range("B2:B12") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add
Key:=Range("C2:C12") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add
Key:=Range("D212") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add
Key:=Range("E2:E12") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Sheet1").Sort
.SetRange Range("A1:E12")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub

 

[Ernest]

Sorry for what undoubtedly a dumb question. I have a large spreadsheet that
I'm trying to create a macro that will sort the data (12,000+ rows) using a
sort with 4 keys. The following code gets me 3 columns - but will not
support the addition of a "Key4".

When I do it manually it's a two stage process - first, sort on the entire
spreadsheet on the first 3 columns - next stage is to then manually page
through the spreadsheet and performing a "mini-sort" on columns 2, 3 and 4
whenever I see the value of column 1 has changed.

I'm thinking I'll need to sort on the column 1 - then loop through the
spreadsheet identifying to identify the first row in which a new value
appears in column one and continue on to identify the last row in which that
same value appears. Then execute a sort on a range defined by the first /
last row identified. Unfortunately, I'm brand new to this VB stuff and
simply don't know how to do this.

Can anybody point me in the right direction?

SpaceNorman

********
Sub NewSort()
Range("SortRange").Select
Selection.Sort Key1:=Range("W5"), Order1:=xlAscending, Key2:=Range("X5") _
, Order2:=xlAscending, Key3:=Range("Y5"), Order3:=xlAscending,
Header:=xlGuess _
, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal, DataOption2:=xlSortTextAsNumbers,
DataOption3 _
:=xlSortNormal
Range("A1").Select
End Sub

declare SortRange