Sort Data With Custom List Excel VBA Macro

Microsoft Excel allows to sort a range based on cell values, cell and font color, and conditional formatting, in ascending or descending order, or using a custom list. In this post we see how to create a custom list of priority categories to sort a range with data accordingly. Excel includes built-in custom lists for weekdays and months. This macro example adds another custom list that is used temporarily to sort the range values. After sorting the table, the custom list is deleted.

Macro code:

 
  Sub SortByPriority()
      Dim PrioList As Variant
 
      'create custom list
      PrioList = Array("Critical", "High", "Medium", "Low")  'change as needed!
      Application.AddCustomList ListArray:=PrioList
 
      'sort range with custom list
      ActiveSheet.UsedRange.Sort _
          Key1:=Range("G1"), _  'change target column as needed!
          Order1:=xlAscending, _
          Header:=xlYes, _
          OrderCustom:=Application.CustomListCount + 1
 
      'delete custom list
      Application.DeleteCustomList Application.CustomListCount + 1
 
  End Sub
 

Macro explained:

• First we declare a Variant variable that stores the array of priority categories for the custom list. A Variant is a special data type that can contain any kind of data. This is the default type Excel uses when a variable is not declared, so we could omit that line with no impact. However, is good to always declare variables.
• Next we use the Array function to create an array of text values, each separated by a comma, that will become the custom list we want to add. Then we can add that array to the custom lists of the application. Excel has some built-in custom lists for weekdays and months (short and long wording) that can be accessed by the corresponding index (1 to 4) as follows:

 For Each elem In Application.GetCustomListContents(4)
     MsgBox elem
 Next elem

• The new custom list is added after the last one. The property CustomListCount gets the total number of custom lists that correspond to that index. The new list corresponds to index 5 if it’s the first one added.
• Now we can sort the range based on values in the target column and following the custom list. In the example above, we target column G to sort the whole table or range by priority from Critical to Low. The order parameter here indicates the order within the array, where ascending goes from lower bound elements to upper bound and descending the other way around. The custom order follows the custom list created earlier and accessed via the index (using CustomListCount).
• If a value is not in the custom list, that row or rows are left at the end for ascending order or come up on top for descending. They follow conventional alphabetical sorting.
• Finally, we delete the custom list by targeting the index. Note that we cannot delete a built-in custom list, and trying to delete a built-in list can prompt an error or corrupt the file.

 

This is how we Sort Data With Custom List in Excel using VBA macros.

Other examples:

• How to Add Slicers To Table Headers with Excel VBA macros
• How to Add Consecutive Numbers with Excel VBA macros
• How to Insert All Pictures In Folder with Excel VBA macros