Sorting a named range using first three columns

[Del Cotter]

I have a macro that reads:

Application.Goto Reference:="SORT_ROWS"
Selection.Sort Key1:=Range("SORT_ROWS"), _
Order1:=xlAscending, Header:=xlNo, _
Orientation:=xlTopToBottom

It sorts the pre-defined range SORT_ROWS by row, using the first column
as the primary key.

How do I get it to sort using the first *three* columns as primary,
secondary and tertiary keys?

 

[Don Guillett]

The macro recorder can be your friend

Sub Macro4()
'
' Macro4 Macro
' Macro recorded 9/6/2008 by Donald B. Guillett
'

'
Range("A1:C4").Select
Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Key2:=Range("B2")
_
, Order2:=xlAscending, Key3:=Range("C2"), Order3:=xlAscending,
Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:=
_
xlSortNormal
End Sub

amend to remove selection. Notice ALL dot placements for the with statement.

With Sheets("yoursheetname")
.Range("A1:C4").Sort Key1:=.Range("A2"), Order1:=xlAscending,
Key2:=.Range("B2") _
, Order2:=xlAscending, Key3:=.Range("C2"), Order3:=xlAscending,
Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:=
_
xlSortNormal
end with

 

[John_John]

Hi!

Take a look at the example.
Created by macro recording.

'
'
Range("A1:C7").Select
Selection.Sort _
Key1:=Range("A2"), Order1:=xlAscending, _
Key2:=Range("B2"), Order2:=xlAscending, _
Key3:=Range("C2"), Order3:=xlAscending, _
Header:=xlGuess, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal, _
DataOption2:=xlSortNormal, _
DataOption3:=xlSortNormal
'
'

I hope it helps.

John

Ο χÏήστης "Del Cotter" έγγÏαψε:

 

[Dave Peterson]

With worksheets("someworksheetnamehere").range("Sort_Rows")
.sort key1:=.columns(1), Order1:=xlAscending, _
key2:=.Columns(2), order2:=xlAscending, _
key3:=.Columns(3), order3:=xlAscending, _
header:=xlNo, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
end with

 

[Del Cotter]

With worksheets("someworksheetnamehere").range("Sort_Rows")
.sort key1:=.columns(1), Order1:=xlAscending, _
key2:=.Columns(2), order2:=xlAscending, _
key3:=.Columns(3), order3:=xlAscending, _
header:=xlNo, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
end with

Thank you! That works great, and so does the column equivalent,
although for the moment I only want to sort by one key in that
direction.

I eliminated the "worksheets" part of the range reference, which I
assume makes the macro work on whatever worksheet is in focus at the
time? (SORT_ROWS is a name local to each sheet, and different for each
sheet)

 

[Del Cotter]

I eliminated the "worksheets" part of the range reference, which I
assume makes the macro work on whatever worksheet is in focus at the
time?

After some reading, I guess it would be better practice to write

With ActiveSheet.Range("SORT_ROWS")

rather than

With .Range("SORT_ROWS")