Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
sorting language
I have a named range ("SortRange") in my '07 wkbk. I need a macro which will
take that range and use the 20th column to sort the whole thing in place, ascending order. Any help on some code for this? Below is what my recorder picked up on a few tries: Sub SumSort() ' ' SumSort Macro ' ' Application.Goto Reference:="SummarySortRange" ActiveWorkbook.Worksheets("Overall YoY Summary").Sort.SortFields.Clear ActiveWorkbook.Worksheets("Overall YoY Summary").Sort.SortFields.Add Key:= _ ActiveCell.Offset(0, 20).Range("A1:A55"), SortOn:=xlSortOnValues, Order:= _ xlAscending, DataOption:=xlSortNormal With ActiveWorkbook.Worksheets("Overall YoY Summary").Sort .SetRange ActiveCell.Range("A1:U55") .Header = xlGuess .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With End Sub Sub Sorter() ' ' Sorter Macro ' Dim SortRange As Range ' ActiveWorkbook.Worksheets("Overall YoY Summary").Sort.SortFields.Clear ActiveWorkbook.Worksheets("Overall YoY Summary").Sort.SortFields.Add Key:= _ Range("a7").Offset(0, 20), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _ xlSortNormal With ActiveWorkbook.Worksheets("Overall YoY Summary").Sort .SetRange Range("A7:U61") .Header = xlGuess .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With End Sub what I'm struggling with is the fact that the range size is not set, and may change. So I don't want the hard references to a sized range, but rather something that will dynamically set the height. For the definition of SortRange, for example, I used =OFFSET('Overall YoY Summary'!$A$7,0,0,COUNTA('Overall YoY Summary'!$A:$A)-3,COUNTA('Overall YoY Summary'!$7:$7)*2-1) and so that takes care of the case that the range grows in height or width. But in code, I don't know how to make this dynamic range. Thanks for any assistance. -- Boris |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
sorting language
The 20th column of the SortRange range?
with worksheets("somesheetnamehere").range("sortrange") .cells.sort key1:=.columns(20), Order1:=xlAscending, _ Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal end with Since it's your data, don't you know whether it has headers or not? Why let excel guess? (This worked in xl2003.) BorisS wrote: I have a named range ("SortRange") in my '07 wkbk. I need a macro which will take that range and use the 20th column to sort the whole thing in place, ascending order. Any help on some code for this? Below is what my recorder picked up on a few tries: Sub SumSort() ' ' SumSort Macro ' ' Application.Goto Reference:="SummarySortRange" ActiveWorkbook.Worksheets("Overall YoY Summary").Sort.SortFields.Clear ActiveWorkbook.Worksheets("Overall YoY Summary").Sort.SortFields.Add Key:= _ ActiveCell.Offset(0, 20).Range("A1:A55"), SortOn:=xlSortOnValues, Order:= _ xlAscending, DataOption:=xlSortNormal With ActiveWorkbook.Worksheets("Overall YoY Summary").Sort .SetRange ActiveCell.Range("A1:U55") .Header = xlGuess .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With End Sub Sub Sorter() ' ' Sorter Macro ' Dim SortRange As Range ' ActiveWorkbook.Worksheets("Overall YoY Summary").Sort.SortFields.Clear ActiveWorkbook.Worksheets("Overall YoY Summary").Sort.SortFields.Add Key:= _ Range("a7").Offset(0, 20), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _ xlSortNormal With ActiveWorkbook.Worksheets("Overall YoY Summary").Sort .SetRange Range("A7:U61") .Header = xlGuess .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With End Sub what I'm struggling with is the fact that the range size is not set, and may change. So I don't want the hard references to a sized range, but rather something that will dynamically set the height. For the definition of SortRange, for example, I used =OFFSET('Overall YoY Summary'!$A$7,0,0,COUNTA('Overall YoY Summary'!$A:$A)-3,COUNTA('Overall YoY Summary'!$7:$7)*2-1) and so that takes care of the case that the range grows in height or width. But in code, I don't know how to make this dynamic range. Thanks for any assistance. -- Boris -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to change German language data into Eglish Language in a colum | Excel Discussion (Misc queries) | |||
Right to Left sorting without changing language settings | Excel Discussion (Misc queries) | |||
language support in excel sheet using a third party language tool | Excel Worksheet Functions | |||
OS language and Office language conflicts | Excel Worksheet Functions | |||
How to change the excel format from language to language? | Excel Discussion (Misc queries) |