In Microsoft Excel, you can create your own custom sort lists and you
can use these lists to specify sort orders when you sort a list of
information. This article describes the procedures for creating a custom
sort list and includes a sample Microsoft Visual Basic for Applications
macro that uses this custom list to sort a range of cells according to the custom sort order.
Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements.
To create a custom sort list to use later with the sample macro:
- In a new worksheet, type the following data:
- On the Tools menu, click Options and in the Options dialog box click the Custom Lists tab.
- In the Import list from cells box, type A1:A5, and click Import.
You should now have a custom sort list based on the list in step 1.
- Click OK.
- Type the following data in a new worksheet. (This data will be the
data that is sorted by the sample macro.)
- In a new module sheet, type the following macro code:
NOTE: The number for the "OrderCustom" argument is the position of
your custom list in the list from the Custom Lists tab of the Options
dialog box. The number you use may be different from the one used in
this example. To find out what number you should use, click Options
on the Tools menu, and click the Custom Lists tab. By default, there
are four default sort lists in Microsoft Excel. Their values for the
"OrderCustom" argument would be 2, 3, 4, and 5, respectively.
Therefore, the first custom sort list would be 6. If you have only
the custom sort list created in steps 1 through 3 above, this is the
value you would use for the "OrderCustom" argument. Use this value
because the number 1 item in the list is reserved for the "New List"
Range("A1").Sort Key1:=Range("A1"), Order1:= _
xlAscending, Header:=xlGuess, OrderCustom:=6, _
MatchCase:=False, Orientation:= xlTopToBottom
- Switch to the worksheet where you entered the value in step 4, and
then run the Custom_Sort macro.
The list should be sorted according to the custom sort list you
created in steps 1-3, and your data should resemble the following:
For additional information about determining the position for a custom sort
list, please click the article number below to view the article in the Microsoft Knowledge Base:
XL: GetCustomListNum Returns Unexpected List Number
For more information about custom lists, click Microsoft Excel Help
menu, type custom sort order
in the Office Assistant or
the Answer Wizard, and then click Search
to view the topics
Article ID: 213625 - Last Review: October 11, 2006 - Revision: 4.2
- Microsoft Excel 2000 Standard Edition
|kbdtacode kbhowto KB213625|