Excel VBA to Custom Sort (5 Easy Examples)

Get FREE Advanced Excel Exercises with Solutions!

Sorting means organizing data in ascending or descending order. So, you can easily visualize sorted extensive data. Also, you can find any information at a glance from that sorted data. Furthermore, you can sort any dataset as you want. With the help of custom sorting, you can sort the data beyond the traditional way. Now, let’s see the details of how to custom-sort using Excel VBA.

In the video, you can see my dataset becomes sorted according to a custom list. So, you can create your own list and can sort the data based on that list.


How to Launch VBA Editor in Excel

To write a VBA code in Excel, you have to launch the VBA editor. For launching this VBA editor, you must need the Developer tab, now if you don’t see the Developer tab then you have to enable the Developer tab.

  • To launch VBA Editor, choose the Developer tab >> then select Visual Basic.

Opening of Visual Basic to custom sort data using VBA in Excel

  • Now, from the Insert tab >> you have to select Module. After inserting the Module, you need to write the preferable code as your dataset.

Inserting Module where you Write Codes

Note: You must save the Excel file as Excel Macro-Enabled Workbook (*xlsm) to run the code.

What Is Custom Sort in Excel?

There are some built-in lists in Excel, based on which you can sort your data. You can sort the dataset either alphabetically (from A to Z) or based on numbers (from highest to lowest) or based on date (from oldest to earlier or vice versa). Also, you can create a list and based on that list you can sort your data, which is called custom sorting. As an example, say you have customers’ information and from them, some are your regular customer. Now, you can sort the database based on these regular customers.


Excel VBA to Custom Sort: 5 Suitable Examples

Here, I will explain some examples of how to custom sort with VBA code in Excel. For your better understanding, I’m going to use a sample dataset that has some information about sales.


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

Here, I have 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.

Now, if you want to sort your data based on a new custom list then you can see this example. Here, I use a simple code so the sort is only according to the created custom list. No other column is considerable for this sorting.

VBA Code to Sort with Custom List in Excel

  • 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

🔎 Code Breakdown:

  • Here, I create a sub-procedure named Custom_Sorting.
  • Then I declare a variable List_Rng as Variant. Where I will keep the list as the base of sorting.
  • In the Sort property, Key denotes the column based on which the sorting will be done. Then, you must mention the Order and Header (generally it assume the Order is in ascending way and there is no Header).
  • Application.DeleteCustomList List_Rng—> this will remove the custom list after sorting. So, when you re-open the Excel file then you will get the original one. If you want to keep the sorted data then remove this line or you can copy-paste the sorted data into a different sheet.
  • Also, if you don’t delete the custom list, then you can’t add this same custom list twice. You must use a different custom list every time.
  • Now, from the Macros >> select Custom_Sorting >> press Run to see the output.
  • So, you will see the following sorted data. If you have any data which doesn’t match the list that data will go to the bottom part of the dataset.

Sorted Data Based on Custom List using VBA in Excel

Read More: Excel VBA to Sort a ComboBox List Alphabetically


2. Creating Custom List for Sorting Dataset Based on Multiple Columns

Here, you can change the order. Also, the prime sorting is based on the created list. Then another sorting happens which is based on Total Sales. Furthermore, you can get your sorted data by clicking on the Sorting button.

So, this is an important example. You can sort properly with the help of two columns and you can also change the sorting order.

Now, let’s see the steps.

Inserting Options Button to custom sort data in Excel VBA

  • Firstly, make the list based on which you want to sort your data.
  • Secondly, from the Developer tab >> go Insert >> choose Option Button (Form Controls) >> drag Option Button >> similarly insert another Option Button.

Linking Cell with Options Button

  • Here, I have renamed the Option Buttons as Ascending and Descending.
  • Then, write down “1” in the D5 cell.
  • Right-click on Ascending >> from the Context Menu Bar >> choose Format Control. As a result, you will see the dialog box named Format Control.
  • Now, from that dialog box, go to the Control menu >> select D5 cell in the Cell link box >> press OK.

VBA Code to Custom Sort in Excel

  • Now, 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

🔎 Code Breakdown:

  • Here, I create a sub-procedure named Custom_Sorting_Multi_Columns.
  • Then I declare two variables my_Rng, and my_List as Range.
  • Then set the range in my_Rng from B8 to the last row of the E column of the dataset (E17).
  • Set the range in my_List from G8 to the last row of the G column of the dataset (G10).
  • Application.AddCustomList will add the my_List range to the custom list.
  • In the Sort property, Key1 denotes the column based on which the prime sorting will be done. And Key2 denotes the dependency of secondary sorting.
  • As I have linked Ascending and Descending to D5 cells so the order will depend on the D5 cell value.
  • Application.DeleteCustomList Application.CustomListCount—> This will remove the custom list after sorting. So, when you re-open the Excel file then you will get the original one. If you want to keep the sorted data then remove this line or you can copy-paste the sorted data into a different sheet.
  • Also, if you don’t delete the custom list, then you can’t add this same custom list twice. You must use a different custom list every time.

Linking Cell to Option Button and Inserting Button

  • Like the previous one, link the Option Button named Descending to the D5 cell.
  • Then, 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. And assign the above Macro named Custom_Sorting_Multi_Columns in the Button.

Renaming of Button

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

 

Note: You can add a custom List once. That’s why all the time, I use Applications.DeleteCustomList. But for this, when you re-open the Excel file then you will lose the sorted data. You have to press Sorting to get the sorted data again.

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

Here, you can see after running the code, some rows get not only hidden but also the remaining data are sorted. Which means you are filtering the data along with sorting.

So, if you want to apply some conditions to filter the data and want to sort that filtered data then this code is perfect for you.

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

VBA Code to Custom Sort along with some Conditions

  • 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

🔎 Code Breakdown:

  • Here, With-End With statement denotes that all the properties are applicable for B4 range of the sheet named “Custom_Sort Using For Loop“. In addition, this code will run only for a sheet named “Custom_Sort Using For Loop“.
  • The first For Next loop will go through all the rows and will check whether any cell of column E is zero. As I use the Offset property so the reference (B4) acts as (0,0) thus column E becomes 3 no column. Here, I use the If-End If statement. EntireRow.Hidden property will hide that cell which contains 0 as cell value.
  • Similarly, with the help of the 2nd For Next loop and If-End If statement, I will hide the rows which contain Jack as a cell value in column D (2nd Column according to reference B4 cell).
  • As the Sort property can’t take more than 3 Keys, I use two Sort properties.
  • In the Sort property, the Key denotes the column based on which the sorting will be done. And Order denotes the way of sorting. If there is a Header in your data then you must mention that too.
  • The 2nd Sort property gets the first priority. The data will sort according to Sales Person (whose name comes alphabetically first), if there are common names then it will check the Date (oldest date will come first), then if there is the same Date it will check the Product name (alphabetically which comes first), still, if the products are same, then it will go to Total sales (largest sales will go first). So, the sorting will follow this serial D column- Sales Person(Ascending) >> B-Date(Ascending) >> C-Product(Ascending) >> E -Total Sales(Descending).
  • Now, from the Macros >> select Sorting_BasedOn_Conditions >> press Run to see the output.
  • Finally, you will see the following sorted data.

Filtering and Sorting

Read More: Excel VBA to Sort Alphabetically


4. Sort Data Within Multiple Columns in Excel VBA

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

So, you can apply multiple Keys in the Sort property with any kind of dataset according to your need.

Code for Sorting Data based on Multiple Columns

  • So, 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

🔎 Code Breakdown:

  • Here, I create a sub-procedure named Sorting_Within_MultipleColumns.
  • Then I declare a variable my_Rng as Range.
  • Then set the range from B4 to the last row of the E column of the dataset (E14). In this way, I make the range dynamic. So, you don’t need to mention how long your dataset is.
  • I use both Sort properties for the same range by using With-End With statement.
  • As the Sort property can’t take more than 3 Keys, I use two Sort properties.
  • In the Sort property, the Key denotes the column based on which the sorting will be done. And Order denotes the way of sorting. If there is a Header in your data then you must mention that too.
  • The 2nd Sort property gets the first priority. So, the data will sort according to Product name (which name comes alphabetically first), if there are common names then it will check the Date (the oldest date will come first) then if there is the same Date it will check Totale Sales (lowest sales will go first), still, if there are same sales amount, then it will go to Sales Person (alphabetically which will come first). So, the sorting will follow this serial C column-Product(Ascending) >> B-Date(Ascending) >> E-Total Sales(Ascending) >> D -Sales Person(Ascending).
  • To see the output, from the Macros >> select Sorting_Within_MultipleColumns >> press Run. So, you will see the following sorted data.

Sorted Data Maintaining 4 Criteria

Read More: Excel VBA Sort Array Alphabetically


5. Sort Data in Excel Table with VBA

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

VBA Code to Sort Data with Excel Table

  • 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

🔎 Code Breakdown:

  • Here, you just need to use the table name inside the Range object.
  • In the Sort property, Key denotes the column based on which the sorting will be done. And Order denotes the way of sorting. If there is a Header in your table then you must mention that too.
  • The Key1 in Sort property gets the first priority. So, the data will sort according to Total Sales (largest sales will go first), if there are same selling prices then it will check Key2 (Date -the oldest date will come first) then if there is the same Date it will check Key3 (Product name- alphabetically which will come first).
  • Here, the third bracket denotes the column header of the table.
  • Now, Run the code and see the output.

Sorted Table

Read More: VBA to Sort Table in Excel


How to Sort Data in Excel Using VBA

In this section, I will describe some basic usage of the Sort property. If you know the basic part of this Sort property then you can use it in a complex way. That’s why I’m trying to demonstrate the details of sorting with different cell values.


1. Sorting Data in Ascending Order

1.1 When Cells Have Text

Here, the D column contains some text values. The code kept the D4 cell in Key1 and so the sorting has done based on column D (Salesperson). In the same way, you can custom sort this data based on the Product name using VBA in Excel also.

VBA code for sorting text in ascending order

  • You may 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
This is a simple code for sorting. Key1 denotes the prime factor based on which the sorting will be done. For text values, it considers the Order1:=xlAscending means A to Z. As the order is ascending that’s why Jack comes first >> John >> Mary. If there is a Header in mentioned range then you must write Header:=xlYes.
  • From Macros >> select sub-procedure named Sorting_Text_in_Ascending >> press Run. Then you will get the following result.

Sorting Data in Ascending Order


1.2 Cells Containing Number

Sorted data if cell contains numeric value

Here, 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.

VBA code for sorting numeric values in ascending order in Excel

  • 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
Key1 denotes the prime factor based on which the sorting will be done. For numeric values, it considers the Order1:=xlAscending means lowest to highest. If there is a Header in mentioned range then you must write Header:=xlYes.
  • After running the code, you will get the following result.

Sorted Data based on Total Sales Column


1.3 When Cells Contain Date

Sorted data if cell contains date value

I have sorted the data based on the date as custom sort using VBA in Excel. So, when you need to focus on date in any type of dataset then you can use this code.

VBA code for sorting dates in ascending order in Excel

  • In Module, write down the code.
Sub Sorting_DateValues_in_Ascending()
 Range("B4:B14").Sort Key1:=Range("B4"), Order1:=xlAscending, Header:=xlYes
End Sub
Key1 denotes the prime factor based on which the sorting will be done. For date values, it considers the Order1:=xlAscending means oldest to newest. If there is a Header in mentioned range then you must write Header:=xlYes.
  • After running the code, you will get the following sorted data.

Sorted Data based on Date Column


2. Sorting Data in Descending Order

Sorting Data in Descending Order when Cell contains text value

You can change the order in Descending way. 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.

VBA code for sorting text in descending order in Excel

  • Use a Module to see the output.
Sub Sorting_Descending_Way()
 Range("C4:C14").Sort Key1:=Range("C4"), Order1:=xlDescending, Header:=xlYes
End Sub
In Sort property, Key1 denotes the reference based on which the sorting will be done. For text values, it considers the Order1:=xlDescending means Z to A. If there is a Header in mentioned range then you must write Header:=xlYes.
  • Now, from Macros >> select the sub-procedure named Sorting_Descending_Way >> press Run. Lastly, you will get the following result.

Sorted Data in Descending Order

Read More: Excel VBA to Sort in Descending Order


How to Sort Data by Double Click in Excel VBA

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

  • Here, I define the dataset as Sales_Data (range B4:E14).

Writing Code inside the Worksheet

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

VBA Code to Run BeforeDoubleClick Event

  • Now, 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

🔎 Code Breakdown:

  • Here, I open an event named BeforeDoubleClick.
  • Then I declare two variables my_Rng as Range, and my_Column as Integer.
  • Then fix my_Column value as the total column number of my defined range (4).
  • Declare a variable Cancel which is assigned to False.
  • If-End If statement says that if you are on 4th row and any column between 1st to 5th then the Cancel value will be True. And the sorting happens.
  • So, click on any column Header to sort the dataset according to that column.

 

Note: You must clear the correct row and column number (by clicking which cell the sorting will be done) under Target.

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


Practice Section

Now, you can practice by yourself.

Practice Section to Custom Sort Data in Excel Using VBA Code


Download Practice Workbook

You can download the practice workbook from here:


Conclusion

Here, I have explained the custom sorting along with the details of the Sort property in Excel VBA. Hopefully, it is clear to you how to custom sort in Excel with VBA. Please, drop comments, suggestions, or queries if you have any in the comment section below.


Related Articles

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.

Tags:

Musiha Mahfuza Mukta
Musiha Mahfuza Mukta

Musiha Mahfuza Mukta is a marine engineer who loves exploring Excel and VBA programming. To her, programming is a time-saving tool for dealing with data, files, and the internet. She's skilled in Rhino3D, Maxsurf, C++, MS Office, AutoCAD, and Excel & VBA, going beyond the basics. With a B.Sc in Naval Architecture & Marine Engineering from BUET, she's shifted gears and now works as a content developer. In this role, she creates techy content exclusively focused on Excel... Read Full Bio

6 Comments
  1. Hi Musiha

    Thank you very much for the great examples.

    BUT, what if I want MARY to appear at the TOP first ?
    How can that be done ?

    Thank you.

    • Reply Avatar photo
      Musiha Mahfuza Mukta Jul 11, 2023 at 11:17 AM

      Thank you, Salome for your comment. I’m very glad that you like the examples.
      Now, come to your question. As my understanding, you need to create a custom list for sorting. In that list MARY should be kept at first. I have explained this in 1st method. You can check this. If you want anything else, please let us know in detail.
      Thank you.

  2. Reply
    Salomé Magerstein Feb 15, 2024 at 5:13 PM

    Hi Musiha

    I only now see your reply to my question of 10 July 2023, please accept my most sincere apology for this late response.

    I have copied your VBA macro and it works great.

    BUT — when I want to save the workbook it closes without saving and then makes my Excel crash. Below is the code I am using :

    Range(“S_O”) = Custom Sort entries (named range)
    Range(“L_D”) = List Data (Data to be sorted : columns B to V (named range)
    Range(“S_X”) = Column for Custom Sort (“V2” named range)

    Sub Sort_Classes() ‘Excel VBA to Sort data in a custom list
    On Error Resume Next
    Dim List_Rng As Variant
    Application.AddCustomList ListArray:=Range(“S_O”)
    List_Rng = Application.GetCustomListNum(Range(“S_O”).Value)
    Range(“L_D”).Sort Key1:=Range(“S_X”), Order1:=xlDescending, _
    Header:=xlYes, Orientation:=xlSortColumns, _
    ordercustom:=List_Rng + 1
    Application.DeleteCustomList List_Rng
    On Error GoTo 0
    End Sub

    Could you maybe help me out on this one?

    Thank you.

    Kind regards

    Salomé

  3. Reply
    Salomé Magerstein Feb 15, 2024 at 5:18 PM

    Hi Musiha

    If you need me to post my actual workbook for a better “investigation”, please let me know where I should send it to.

    Thank you very much.

    Awaiting your soonest reply.

    Kind regards

    Salomé

  4. Reply
    Salomé Magerstein Feb 15, 2024 at 11:33 PM

    Hi Mushiha

    Don’t worry about getting back to me. I got it working. You MUST add this line “activesheet.Sort.SortFields.Clear” BEFORE you can delete the custom sort list AND custom lists do not work with formulas in a cell. It MUST be values only.

    Thank you in any way for putting my on the right path with your examples though.

    Have a great weekend ahead.

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo