Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Hiding columns in a named range | Excel Programming | |||
sorting named range data | Excel Programming | |||
unhide columns in named range | Excel Programming | |||
Sorting Named Range | Excel Programming | |||
sorting named range | Excel Programming |