XL2000: Sample Macro to Sort List Based on Custom Sort Order

Article translations Article translations
Article ID: 213625 - View products that this article applies to.
This article was previously published under Q213625
Expand all | Collapse all


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:
  1. In a new worksheet, type the following data:
          A1: red
          A2: blue
          A3: green
          A4: yellow
          A5: pink
  2. On the Tools menu, click Options and in the Options dialog box click the Custom Lists tab.
  3. 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.
  4. Click OK.
  5. Type the following data in a new worksheet. (This data will be the data that is sorted by the sample macro.)
          A1: pink
          A2: blue
          A3: green
          A4: blue
          A5: yellow
          A6: pink
          A7: red
          A8: blue
          A9: red
  6. In a new module sheet, type the following macro code:
          Sub Custom_Sort()
             Range("A1").Sort Key1:=Range("A1"), Order1:= _
                xlAscending, Header:=xlGuess, OrderCustom:=6, _
                MatchCase:=False, Orientation:= xlTopToBottom
          End Sub
    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" item.
  7. 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:
          A1: red
          A2: red
          A3: blue
          A4: blue
          A5: blue
          A6: green
          A7: yellow
          A8: pink
          A9: pink
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:
134913 XL: GetCustomListNum Returns Unexpected List Number


For more information about custom lists, click Microsoft Excel Help on the Help menu, type custom sort order in the Office Assistant or the Answer Wizard, and then click Search to view the topics returned.


Article ID: 213625 - Last Review: October 11, 2006 - Revision: 4.2
  • Microsoft Excel 2000 Standard Edition
kbdtacode kbhowto KB213625

Give Feedback


Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com