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

You had some resonses to this question in your first post.
--
jb


"Michael Koerner" wrote:

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

Sorry about that. It did not show up when I first checked for it

--

Regards
Michael Koerner


"john" wrote in message
...
You had some resonses to this question in your first post.
--
jb


"Michael Koerner" wrote:

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

and another ....

Range(Range("A1"), Range("G1").End(xlDown) ).Sort Key1:=Range("E2"),
Order1:=xlDescending, Key2:= Range("B2"), Order2:=xlAscending,
Key3:=Range("C2"), Order3:=xlAscending



"Michael Koerner" wrote:

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