Macro Help

[Michael Koerner]

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

 

[JLGWhiz]

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

 

[john]

Not tested but give this a try.
I have added some code that creates a dynamic named range. This will
automatically expand the selection as you add more rows to your worksheet.
I have also qualified the Range references to a worksheet. If your worksheet
is not named “Sheet1†then you will need to amend code where I have indicated.

Hope works ok.

Sub SortShoppingList()
Dim ws1 As Worksheet

'change sheet name as required
Set ws1 = Worksheets("Sheet1")

ActiveWorkbook.Names.Add Name:="ListRange", _
RefersToR1C1:= _
"=OFFSET(" & ws1.Name & "!R1C1,0,0,COUNTA(" &
ws1.Name & "!C1),7)"


ws1.Range("A1").AutoFilter _
Field:=4, _
Criteria1:="<>", _
VisibleDropDown:=False


ws1.Range("ListRange").Sort Key1:=ws1.Range("E2"), _
Order1:=xlDescending, Key2:= _
ws1.Range("B2"), _
Order2:=xlAscending, _
Key3:=ws1.Range("C2"), _
Order3:=xlAscending _
, Header:=xlGuess, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:= _
xlTopToBottom, _
DataOption1:=xlSortNormal, _
DataOption2:=xlSortNormal, _
DataOption3:=xlSortNormal

ws1.Range("A1").AutoFilter

End Sub

 

[JLGWhiz]

Forgot to put the last row variable in:

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
lr = sh.Cells(Rows.Count, 1).End(xlUp).Row
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]

Thanks very much, Sorry about the double post

--

Regards
Michael Koerner


Forgot to put the last row variable in:

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
lr = sh.Cells(Rows.Count, 1).End(xlUp).Row
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]

Thanks, will give it a shot

--

Regards
Michael Koerner


Not tested but give this a try.
I have added some code that creates a dynamic named range. This will
automatically expand the selection as you add more rows to your worksheet.
I have also qualified the Range references to a worksheet. If your worksheet
is not named "Sheet1" then you will need to amend code where I have
indicated.

Hope works ok.

Sub SortShoppingList()
Dim ws1 As Worksheet

'change sheet name as required
Set ws1 = Worksheets("Sheet1")

ActiveWorkbook.Names.Add Name:="ListRange", _
RefersToR1C1:= _
"=OFFSET(" & ws1.Name & "!R1C1,0,0,COUNTA(" &
ws1.Name & "!C1),7)"


ws1.Range("A1").AutoFilter _
Field:=4, _
Criteria1:="<>", _
VisibleDropDown:=False


ws1.Range("ListRange").Sort Key1:=ws1.Range("E2"), _
Order1:=xlDescending, Key2:= _
ws1.Range("B2"), _
Order2:=xlAscending, _
Key3:=ws1.Range("C2"), _
Order3:=xlAscending _
, Header:=xlGuess, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:= _
xlTopToBottom, _
DataOption1:=xlSortNormal, _
DataOption2:=xlSortNormal, _
DataOption3:=xlSortNormal

ws1.Range("A1").AutoFilter

End Sub

 

[Michael Koerner]

Gave it a try, It worked, but it gave me all the items in the list not just
the items selected for the shopping list from the store locations.

--

Regards
Michael Koerner


Not tested but give this a try.
I have added some code that creates a dynamic named range. This will
automatically expand the selection as you add more rows to your worksheet.
I have also qualified the Range references to a worksheet. If your worksheet
is not named "Sheet1" then you will need to amend code where I have
indicated.

Hope works ok.

Sub SortShoppingList()
Dim ws1 As Worksheet

'change sheet name as required
Set ws1 = Worksheets("Sheet1")

ActiveWorkbook.Names.Add Name:="ListRange", _
RefersToR1C1:= _
"=OFFSET(" & ws1.Name & "!R1C1,0,0,COUNTA(" &
ws1.Name & "!C1),7)"


ws1.Range("A1").AutoFilter _
Field:=4, _
Criteria1:="<>", _
VisibleDropDown:=False


ws1.Range("ListRange").Sort Key1:=ws1.Range("E2"), _
Order1:=xlDescending, Key2:= _
ws1.Range("B2"), _
Order2:=xlAscending, _
Key3:=ws1.Range("C2"), _
Order3:=xlAscending _
, Header:=xlGuess, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:= _
xlTopToBottom, _
DataOption1:=xlSortNormal, _
DataOption2:=xlSortNormal, _
DataOption3:=xlSortNormal

ws1.Range("A1").AutoFilter

End Sub