Excel VBA to Custom Sort: 5 Easy Methods

Method 1 –  Creating Custom List to Sort Dataset Based on Single Column in Excel

We created a list within G5:G8 cells. The list has three names, they are Mary, Jack, and John. So, the sort was based on this list.

If you want to sort your data based on a new custom list; you can see this example. Use a simple code so the sort is only according to the created custom list. No other column is considerable for this sorting.

• From Developer tab >> select Visual Basic >> then from the Insert tab >> you have to select Module >> write down the following code in the Module.

Sub Custom_Sorting()
Dim List_Rng As Variant
 Application.AddCustomList ListArray:=Range("G5:G7")
 List_Rng = Application.GetCustomListNum(Range("G5:G7").Value)
 Range("B4:E14").Sort Key1:=Range("D5"), Order1:=xlAscending, _
        Header:=xlYes, Orientation:=xlSortColumns, _
        OrderCustom:=List_Rng + 1
 Application.DeleteCustomList List_Rng
End Sub

• From the Macros >> select Custom_Sorting >> press Run to see the output.
• See the following sorted data. If you have any data that doesn’t match the list, it will go to the bottom of the dataset.

Method 2 – Creating Custom List for Sorting Dataset Based on Multiple Columns

Change the order. The prime sorting is based on the created list. Another sorting happens, which is based on Total Sales. You can get your sorted data by clicking on the Sorting button.

Two columns help you sort properly, and you can also change the sorting order.

Now, let’s see the steps.

• Make the list based on which you want to sort your data.
• From the Developer tab >> go Insert >> choose Option Button (Form Controls) >> drag Option Button >> insert another Option Button.

• Rename the Option Buttons as Ascending and Descending.
• Write down “1” in the D5 cell.
• Right-click on Ascending >> from the Context Menu Bar >> choose Format Control. You will see the dialog box named Format Control.
• From that dialog box, go to the Control menu >> select D5 cell in the Cell link box >> press OK.

• Insert a new Module and write down the following code in that.

Sub Custom_Sorting_Multi_Columns()
Dim my_Rng As Range
Dim my_List As Range
Set my_Rng = Range("B8", Range("E" & Rows.Count).End(xlUp))
Set my_List = Range("G8", Range("G8").End(xlDown))
 Application.AddCustomList my_List
 my_Rng.Sort key1:=[D8], order1:=1, ordercustom:=Application.CustomListCount + 1, key2:=[E8], order2:=[D5]
 Application.DeleteCustomList Application.CustomListCount
End Sub

• Like the previous one, link the Option Button named Descending to the D5 cell.
• From the Developer tab >> go to Insert >> select Button (Form Controls).
• Drag that Button on the D5 cell, as the user can’t see the D5 cell value; assign the above Macro named Custom_Sorting_Multi_Columns in the Button.

• Change the name of the Button >> right-click on Button (Form Control) >> from the Context Menu Bar >> select Edit Text. We named the Button to “Sorting”.
• Select any Option Button (either Ascending or Descending) >> then press on Sorting >> you will get the sorted data.

Method 3 – Using For Next Loop to Custom Sort Multiple Columns Based on Conditions

You can see after running the code some rows get not only hidden but also the remaining data are sorted. You are filtering the data along with sorting.

Apply some conditions to filter the data and sort that filtered data then this code is perfect for you.

You have a very long dataset, and you don’t need all that data; in that case, you can use this code.

• Use this code in a new Module to see the outcome.

Sub Sorting_BasedOn_Conditions()
Dim my_Variable As Long
With Sheets("Custom_Sort Using For Loop").Range("B4")
    For my_Variable = .CurrentRegion.Rows.Count - 1 To 1 Step -1
        If .Offset(my_Variable, 3) = 0 Then
            .Rows(my_Variable + 1).EntireRow.Hidden = True
        End If
    Next my_Variable
    For my_Variable = .CurrentRegion.Rows.Count - 1 To 1 Step -1
        If .Offset(my_Variable, 2) = "Jack" Then
            .Rows(my_Variable + 1).EntireRow.Hidden = True
        End If
    Next my_Variable
    .CurrentRegion.Sort Key1:=Range("E4"), Order1:=xlDescending, Header:=xlYes
    .CurrentRegion.Sort Key1:=Range("D4"), Order1:=xlAscending, _
                        Key2:=Range("B4"), Order2:=xlAscending, _
                        Key3:=Range("C4"), Order3:=xlAscending, _
                        Header:=xlYes
End With
End Sub

• From the Macros >> select Sorting_BasedOn_Conditions >> press Run to see the output.
• You will see the following sorted data.

Method 4 – Sort Data Within Multiple Columns in Excel VBA

Sort your data based on multiple columns. With this kind of sorting, you can find which product sells on which date. Get a clear idea about total sales based on product and date at a glance.

Apply multiple Keys in the Sort property with any kind of dataset according to your need.

• Write down the code given below in a new Module.

Sub Sorting_Within_MultipleColumns()
Dim my_Rng As Range
Set my_Rng = Range("B4", Range("E" & Rows.Count).End(xlUp))
With my_Rng
 .Sort Key1:=Range("B4"), Order1:=xlAscending, _
    Key2:=Range("E4"), Order2:=xlAscending, _
    Key3:=Range("D4"), Order3:=xlAscending, _
    Header:=xlYes
    .Sort Key1:=Range("C4"), Order1:=xlAscending, _
    Header:=xlYes
End With
End Sub

• From the Macros >> select Sorting_Within_MultipleColumns >> press Run. You will see the following sorted data.

Method 5 –  Sort Data in Excel Table with VBA

If you have an Excel table, you can also sort it. You can apply multiple Keys in the Sort property with an Excel table, too.

• Write down the corresponding code Module.

Sub Sorting_in_Table()
With Range("Sales_Table")
.Sort Key1:=Range("Sales_Table[Total Sales]"), Order1:=xlDescending, _
    Key2:=Range("Sales_Table[Date]"), Order2:=xlAscending, _
    Key3:=Range("Sales_Table[Product]"), Order3:=xlAscending, _
    Header:=xlYes
End With
End Sub

• Run the code and see the output.

How to Sort Data in Excel Using VBA

Method 1 – Sorting Data in Ascending Order

1.1 When Cells Have Text

The D column contains some text values. The code kept the D4 cell in Key1 and so the sorting was done based on column D (Salesperson). You can custom sort this data based on the Product name using VBA in Excel also.

• Write this code in Module and check the sorting.

Sub Sorting_Text_in_Ascending()
 Range("D4:D14").Sort Key1:=Range("D4"), Order1:=xlAscending, Header:=xlYes
End Sub

• From Macros >> select sub-procedure named Sorting_Text_in_Ascending >> press Run. You will get the following result.

1.2 Cells Containing Number

The E column contains some numeric values as currency. The code kept E4 cell in Key1 and so the sorting has done based on column E (Total Sales).

You can custom sort students’ marks to see the highest mark holder, employee’s ID, student’s ID, serial numbers, and any type of numeric value in Excel VBA.

• In a Module, use this code to check the output.

Sub Sorting_NumericValues_in_Ascending()
 Range("E4:E14").Sort Key1:=Range("E4"), Order1:=xlAscending, Header:=xlYes
End Sub

• After running the code, you will get the following result.

1.3 When Cells Contain Date

We sorted the data based on the date as a custom sort using VBA in Excel. When you need to focus on date in any type of dataset then you can use this code.

• In Module, write down the code.

Sub Sorting_DateValues_in_Ascending()
 Range("B4:B14").Sort Key1:=Range("B4"), Order1:=xlAscending, Header:=xlYes
End Sub

• After running the code, you will get the following sorted data.

Method 2 – Sorting Data in Descending Order

You can change the order in Descending order. Suppose you want to see the earlier date first, then you should use Descending order. For alphabetic values, descending means Z to A, date—newest to oldest, and numbers—largest to lowest.

• Use a Module to see the output.

Sub Sorting_Descending_Way()
 Range("C4:C14").Sort Key1:=Range("C4"), Order1:=xlDescending, Header:=xlYes
End Sub

• From Macros >> select the sub-procedure named Sorting_Descending_Way >> press Run. You will get the following result.

How to Sort Data by Double Click in Excel VBA

Sorting is a necessary thing for any dataset. In the case of a large dataset, sorting is a must. The easier the way is to sort, it will be more helpful. What if you can sort the data with a click on the mouse? So, here I will show you the code to sort your data quickly.

• Define the dataset as Sales_Data (range B4:E14).

• You have to write the code in the particular sheet. As I’m going to use an event in the code. So, right-click on the sheet name >> choose View Code from the Context Menu Bar.

• Write the following code in that event.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim my_Rng As Range
Dim my_Column As Integer
my_Column = Range("Sales_Data").Columns.Count
Cancel = False
If Target.Row = 4 And Target.Column <= my_Column + 1 Then
    Cancel = True
    Set my_Rng = Range(Target.Address)
    Range("Sales_Data").Sort Key1:=my_Rng, Header:=xlYes
End If
End Sub

• Click on any column Header to sort the dataset according to that column.

Frequently Asked Questions

1. Can you create your own custom list for sorting in Excel?

Yes, you can create your own custom list for sorting in Excel. You can see the 1st or 2nd examples for that purpose.

2. How do I sort A to Z in Excel VBA?

To sort from A to Z, you can sort data in ascending order in Excel VBA. You can check this section “When Cells Have Text Values”.

Download Practice Workbook

You can download the practice workbook from here:

Related Articles

• How to Sort Array with Excel VBA
  
• Excel VBA to Sort Multidimensional Array