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 |
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 |
All times are GMT +1. The time now is 09:09 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com