Sorting a named range using first three columns
 

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?

--
Del Cotter
NB Personal replies to this post will send email to ,
which goes to a spam folder-- please send your email to del3 instead.

Sorting a named range using first three columns
 

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


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Del Cotter" wrote in message
...

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?

--
Del Cotter
NB Personal replies to this post will send email to
,
which goes to a spam folder-- please send your email to del3 instead.

Sorting a named range using first three columns
 

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" Î*γγÏ�αψε:


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?

--
Del Cotter
NB Personal replies to this post will send email to ,
which goes to a spam folder-- please send your email to del3 instead.

Sorting a named range using first three columns
 

With worksheets("someworksheetnamehere").range("Sort_Ro ws")
.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 wrote:

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?

--
Del Cotter
NB Personal replies to this post will send email to ,
which goes to a spam folder-- please send your email to del3 instead.

--

Dave Peterson

Sorting a named range using first three columns
 

On Sat, 6 Sep 2008, in microsoft.public.excel.programming,
Dave Peterson said:

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

With worksheets("someworksheetnamehere").range("Sort_Ro ws")
.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
NB Personal replies to this post will send email to ,
which goes to a spam folder-- please send your email to del3 instead.

Sorting a named range using first three columns
 

On Sun, 7 Sep 2008, in microsoft.public.excel.programming,
Del Cotter said:
Dave Peterson said:
How do I sort using the first *three* columns as primary,
secondary and tertiary keys?

With worksheets("someworksheetnamehere").range("Sort_Ro ws")

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")

--
Del Cotter
NB Personal replies to this post will send email to ,
which goes to a spam folder-- please send your email to del3 instead.