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

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