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

ID: Q142112

The information in this article applies to:

Microsoft Excel 97 for Windows
Microsoft Excel for Windows 95, version 7.0
Microsoft Excel for Windows, versions 5.0, 5.0c
Microsoft Excel for the Macintosh, versions 5.0, 5.0a
Microsoft Excel 98 Macintosh Edition

SUMMARY

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 (Sub procedure) that uses this custom list to sort a range of cells according to the custom sort order.

MORE INFORMATION

Microsoft provides programming examples for illustration only, without warranty either expressed or implied, including, but not limited to, the implied warranties of merchantability and/or fitness for a particular purpose. This article assumes that you are familiar with the programming language being demonstrated and the tools used to create and debug procedures. Microsoft support professionals 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 needs. If you have limited programming experience, you may want to contact the Microsoft fee-based consulting line at (800) 936-5200. For more information about the support options available from Microsoft, please see the following page on the World Wide Web:

   http://www.microsoft.com/support/supportnet/refguide/

To create a custom sort list that will be used later with the sample macro, follow these steps:

1. In a new worksheet, enter the following:

      A1: red
      A2: blue
      A3: green
      A4: yellow
      A5: pink

2. On the Tools menu, click Options (Preferences if you are using Excel

   for the Macintosh), and in the Options (Preferences) dialog box click
   the Custom Lists tab.

3. In the Import List From Cells box, type A1:A5, click Import, and

   then click OK.

   You should now have a custom sort list based on the list in step 1.

4. Enter 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

5. 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.

6. Activate 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 on determining the position for a custom sort list, see the following article in the Microsoft Knowledge Base:

   ARTICLE-ID: Q134913
   TITLE     : XL: GetCustomListNum Returns Unexpected List Number

REFERENCES

For more information about Custom Lists, click Answer Wizard on the Help menu and type:

   tell me about creating custom lists

For more information about Custom Lists, choose the Search button in Help and type:

   custom lists

Additional query words: 5.00 5.00a 5.00c 7.00 8.00 XL97

Keywords          : kbualink97 kbdta kbdtacode PgmHowto KbVBA 
Version           : WINDOWS: 5.0,5.0c,7.0,97; MACINTOSH: 5.0,5.0a,98
Platform          : MACINTOSH WINDOWS
Issue type        : kbhowto

Last Reviewed: May 17, 1999