Excel Custom Sort with Excel VBA
Excel has the ability to sort data into an order of your choosing. The custom sort can be achieved with the assistance of a customised list. Many will be familiar with the list the automates months. If you type Jan in a cell and drag that cell it will produce the months of the year. This is because of a custom list.
When creating a custom sort the first thing you need to create is a customised list, a list which displays the order you want your data to appear in. To set up a custom sort in Excel;
On the Home tab choose Sort & Filter
Click on the Custom Sort icon. This will take you to the following dialog.
On the drop down on the right of screen choose Custom List...
On the right of screen click Add and where it says List entries, type your new list.
Click OK and the list will be transferred from the List Entries box, to the Custom Lists Box above.
You will notice after you click OK the custom list will appear in the Order box to the right. Choose the Column you wish to sort by and click OK.
That will result in my data being sorted first by Apples, then Pears, then Bananas and last of all will be Oranges.
Excel Manage a Custom List
You may wish to manage a custom list from a list in Excel. This will make the custom list more dynamic and a lot easier to manage. You can add to the list and change the order of the list as the requirements of your business change. The way to achieve this task is to more formally add to the custom list function.
Choose File - Options - Advanced
Click on the Edit Custom List button.
In the Import List from Cells box type or refer the cells with the Red reference box. Click OK and your in cell custom list will be added.
Excel Custom Sort Using VBA
If you want to automate this task in Excel with VBA the following is a an example of a procedure which uses a custom list and sorts the list on two levels. The first level is to sort by the custom list and the second level is to sort the list in ascending or descending order. This is a screen shot of the procedure in the file below.
Dim r As Range
Dim rng As Range
Set r=Range("B10", Range("D" & Rows.Count).End(xlUp))
Set rng=Sheet2.Range("A2", Sheet2.Range("A2").End(xlDown))
r.Sort key1:=[B10], order1:=1, ordercustom:=Application.CustomListCount + 1, key2:=[D10], order2:=[B1]
Application.DeleteCustomList Application.CustomListCount
The following Excel VBA procedure outlines the custom sort. It takes a simple dataset and sorts according to a list on another sheet.
