Macro Assist

[Michael Koerner]

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

 

[Nigel]

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
(e-mail address removed)



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

 

[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


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
(e-mail address removed)



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