CurrentRegion in 2007 Sort

[Andy]

I used to add currentregion to a sort macro in 2003 so that as the list
increased, the macro would automatically include the new items. I can't
figure out how to do that in 2007. Below is a sample recorded macro with a
specified range. How do I make it "grow" automatically as the list grows?
Thanks.

Sub Macro2()
ActiveWorkbook.Worksheets("Quarter 1").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Quarter 1").Sort.SortFields.Add
Key:=Range("E4"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Quarter 1").Sort
.SetRange Range("A4:G78")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub

 

[Dave Peterson]

Untested...

I'd qualify all the ranges:

Sub Macro2()
with ActiveWorkbook.Worksheets("Quarter 1")
.Sort.SortFields.Clear
.Sort.SortFields.Add _
Key:=.Range("E4"), _
SortOn:=xlSortOnValues, _
Order:=xlAscending, _
DataOption:=xlSortNormal
with .sort
.SetRange .Range("A4").currentregion
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
end with
End Sub

But there's no reason you can't use the old xl2003 syntax:

with ActiveWorkbook.Worksheets("Quarter 1")
with .range("a4").currentregion
.sort key1:=.columns(5), _
order1:=xlAscending, _
header:=xlYes, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom
end with
end with