Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Custom Sort Order
I have a spreadsheet (no headers) with project names listed in col. A and a
classification in col. B, as follows: Project1 WUC Project2 A Project3 B Project4 WUC Project5 A Project6 A Project7 WUC Project8 B Projects are added frequently to this list by users, and my goal is a VBA solution to sort the list in a custom order: WUC, then A, then B. To get myself started, I recorded a macro where I created a custom list, then sorted by that list. I noticed the recorded code makes a reference to "OrderCustom:=7", which I realize is the desired position in MY custom list. However, I know that not every user has the same custom lists I have, so I how do I adapt my code to help other users sort by the same criteria? Here's part of my recorded code: Application.AddCustomList ListArray:=Array("WUC", "A", "B") 'Code to select range of projects Selection.Sort Key1:=Range("B1"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=7, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal I realize the answer may have nothing to do with creating a custom list in the first place. Thanks for any help to get me on the right track! -- Steve C |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Custom Sort Order
I have come up with my own solution:
Sub CreateOrder() Dim N As Integer Application.AddCustomList ListArray:=Array("WUC", "A", "B") N = Application.GetCustomListNum(Array("WUC", "A", "B")) + 1 '1 added to give true position of desired order in custom list 'Code to select desired sort range Selection.Sort Key1:=Range("B1"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=N, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal End Sub -- Steve C "Steve C" wrote: I have a spreadsheet (no headers) with project names listed in col. A and a classification in col. B, as follows: Project1 WUC Project2 A Project3 B Project4 WUC Project5 A Project6 A Project7 WUC Project8 B Projects are added frequently to this list by users, and my goal is a VBA solution to sort the list in a custom order: WUC, then A, then B. To get myself started, I recorded a macro where I created a custom list, then sorted by that list. I noticed the recorded code makes a reference to "OrderCustom:=7", which I realize is the desired position in MY custom list. However, I know that not every user has the same custom lists I have, so I how do I adapt my code to help other users sort by the same criteria? Here's part of my recorded code: Application.AddCustomList ListArray:=Array("WUC", "A", "B") 'Code to select range of projects Selection.Sort Key1:=Range("B1"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=7, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal I realize the answer may have nothing to do with creating a custom list in the first place. Thanks for any help to get me on the right track! -- Steve C |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Custom Sort order issue | Excel Discussion (Misc queries) | |||
Add sort order to custom header? | Excel Discussion (Misc queries) | |||
Macro with custom sort order | Excel Worksheet Functions | |||
Custom Sort Order | Excel Discussion (Misc queries) | |||
VBA to sort automatically in a custom order | Excel Programming |