Try this(untested):

Sub SortShoppingList()
'
' SortShoppingList Macro
' Macro recorded 15/12/2006 by Michael Koerner
'

'
Dim rng As Range, lr As Long, sh As Worksheet
Set sh = ActiveSheet
Set rng = sh.Range("A1:G" & lr)
rng.AutoFilter Field:=4, Criteria1:="<"
Range("A1:G" & lr).Sort Key1:=Range("E2"), Order1:=xlDescending, 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




"Michael Koerner" wrote in message
...
I have a macro which sorts a shopping list in the order of the stores that
I
do my grocery shopping, that I created using the create a macro option in
I
believe 2003, now using 2007, code below. Unfortunately if I add more
items
to the list and forget to change the macro end range things don't get
sorted
in the proper order. Is there any way that I can change the range so that
regardless of the number of items in the list the macro will work
properly?

Sub SortShoppingList()
'
' SortShoppingList Macro
' Macro recorded 15/12/2006 by Michael Koerner
'

'
Range("A1").Select
Selection.AutoFilter Field:=4, Criteria1:="<"
Range("A1:G1741").Sort Key1:=Range("E2"), Order1:=xlDescending, 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


--

Regards
Michael Koerner