I have a grocery store shopping list and I would like to set up a macro that would sort and display the list, if there is a value in column D which has a header name of Qty. I would like it sorted by store name/number (in reverse order ), then by Name, Aisle, and Depth. Below is the code I recorded only if there is a number greater than 1 it puts it at the end of the list since I added a new store.

The sheet headings are Item, Aisle, Depth, Store, Price, Notes. Any help as always is greatly appreciated.

Sub SortShoppingList()
'
'
'

'
Range("A1").Select
Selection.AutoFilter Field:=4, Criteria1:="<"
Range("A1:G1024").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

No sure what the issue is here? You can sort on multiple keys each in any order. Please explain more what problem you are facing.

--

Regards,
Nigel




"Michael Koerner" wrote in message ...
I have a grocery store shopping list and I would like to set up a macro that would sort and display the list, if there is a value in column D which has a header name of Qty. I would like it sorted by store name/number (in reverse order ), then by Name, Aisle, and Depth. Below is the code I recorded only if there is a number greater than 1 it puts it at the end of the list since I added a new store.

The sheet headings are Item, Aisle, Depth, Store, Price, Notes. Any help as always is greatly appreciated.

Sub SortShoppingList()
'
'
'

'
Range("A1").Select
Selection.AutoFilter Field:=4, Criteria1:="<"
Range("A1:G1024").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

So long as there is only a value of one (1) in the Qty column, the macro works just fine. If there is a different Qty amount The different items are put on the bottom of the list.

I know diddly about recording macros. What I would like it to do, if there is a value in the Qty Col C column then these are the only items I want to see after I do a sort using Stores (in reverse order) Col "E", Item description Col "A", Isle number Col "C", and Depth Col "D"

This produces a shopping list which I then print and use, then Exit Excel without saving the changes that the macro produces. Hope this helps

--

Regards
Michael Koerner


"Nigel" wrote in message ...
No sure what the issue is here? You can sort on multiple keys each in any order. Please explain more what problem you are facing.

--

Regards,
Nigel




"Michael Koerner" wrote in message ...
I have a grocery store shopping list and I would like to set up a macro that would sort and display the list, if there is a value in column C which has a header name of Qty. I would like it sorted by store name/number (in reverse order ), then by Name, Aisle, and Depth. Below is the code I recorded only if there is a number greater than 1 it puts it at the end of the list since I added a new store.

The sheet headings are Item, Aisle, Depth, Qty, Store, Price, Notes. Any help as always is greatly appreciated.

Sub SortShoppingList()
'
'
'

'
Range("A1").Select
Selection.AutoFilter Field:=4, Criteria1:="<"
Range("A1:G1024").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