Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Help
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Help
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Help
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 -- 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Help
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 "JLGWhiz" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Help
Thanks very much, Sorry about the double post
-- Regards Michael Koerner "JLGWhiz" wrote in message ... 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 "JLGWhiz" wrote in message ... 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Help
Thanks, will give it a shot
-- Regards Michael Koerner "john" wrote in message ... 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 -- 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Help
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 "john" wrote in message ... 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 -- 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro recorded... tabs & file names changed, macro hangs | Excel Worksheet Functions | |||
Macro to copy and paste values (columns)I have a macro file built | Excel Programming | |||
Macro Help Needed - Excel 2007 - Print Macro with Auto Sort | Excel Worksheet Functions | |||
macro to delete entire rows when column A is blank ...a quick macro | Excel Programming | |||
Start Macro / Stop Macro / Restart Macro | Excel Programming |