sorting language

G

Guest

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.
 
D

Dave Peterson

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Applying Variables to SORT 4
Undo Macro Action 3
VBA 2 Codes 2
Clear Check Box 2
Problem with Worksheet Activate and Sorting 7
Sort by one column then another. 2
Sorting Question 5
Sort Dynamic Range 4

Top