What is Sorting?

Sorting is simply the process of rearranging data in ascending or descending order. The data is organized in alphabetical order when dealing with strings or words. Two options are available: sorting from A-Z (ascending order) or Z-A (descending order).

Sorting in Microsoft Excel Spreadsheet

Most of us are familiar with using the “Sort” option in Microsoft Excel. The data in the range gets sorted by selecting the desired area to be sorted and choosing the “Sort” option under the “Data” menu. Excel offers two options: sorting A-Z and Z-A and a Sort Dialog that allows sorting on multiple columns and levels. This feature is very useful and has greatly facilitated my work many times.

Sorting in Excel Using VBA

To achieve the required data sorting in a macro, we use the Range.Sort method. Let’s understand how this method works.

A range represents a collection of cells, such as the range of cells from B1 to B9 (i.e., Range(“B1:B9”)) shown in the image above. We can also define a range using a named range and use it in the sort method.

Syntax:

Range("<table to be sorted>").Sort([Key1], [Order1], <Header>)

Where:

“<table to be sorted>” is the range of cells or the table in which one or more columns are to be sorted.

[Key1] is the column to be sorted.

[Order1] is the ascending/descending order in which the column in Key1 is sorted. Possible options for this are “xlAscending” and “xlDescending.”

<Header> is used to determine whether the header is included in the selected/specified range or not. This is done using the options “xlYes” and “xlNo.”

Sorting on a Single Column

Example 1: Sorting Including the Header in Ascending Order.

Let’s try to sort the above table using the parameter value “xlNo” for the Header while actually including the header in the range.

There are some demonstrations in this article on using the parameters in the best way. Just like the icons on the menu, VBA also facilitates sorting on single or multiple columns in a desired way. Let’s explore them one by one.

Sub sort_demo()
    ' Declaration of variables - a range named myrange1 is declared here.
    Dim myrange1 As Range
    
    ' Defining the range1 range variable - the header is actually included here.
    Set myrange1 = Range("A1:C9")
    
    ' Sorting the data in the range based on Column B of the named range (in ascending order).
    myrange1.Sort Key1:=Range("B1"), Order1:=xlAscending, Header:=xlNo
End Sub

Output:

The above output shows that the header is also included in the sorting process because we used the parameter “xlNo” while the header was a part of the selected named range.

Now, in the next example, let us mention that the header is a part of the range in the parameter. i.e., we will use the “xlYes” value for the parameter.

Example 2: Sorting Excluding the Header in Ascending Order.

To retain the header as the first row, I have moved the header up again. So, the sample data looks like this now:

The code is as below. Here we have made minor changes to the code. This time, the sorting will happen based on Column A instead of Col B as it is already sorted in ascending order. Also, in the header parameter, we will use the value “xlYes” to prevent the header from getting sorted.

Sub sort_demo()
    ' Declaration of variables - a range named myrange2 is declared here.
    Dim myrange2 As Range
    
    ' Defining the range2 range variable - the header is actually included here.
    Set myrange2 = Range("A1:C9")
    
    ' Sorting the data in the range based on Column A of the named range (in ascending order).
    myrange2.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlYes
End Sub

Output after running the code:

In this output, as we expected, the header is intact and is not a part of the sort/shuffling. The rows of data are sorted in ascending order based on the S.no, i.e., column A of the named range.

Example 3: Sorting Excluding the Header in Descending Order.

Let’s try sorting the same table on Column C in descending order.

Sub sort_demo()

' declaration of variables - a range named myrange3 is declared here.
Dim myrange3 As Range

' defining the range1 range variable - header is actually included here
Set myrange3 = Range("A1:C9")

' Sorting the data in the range based on Column A of the named range ( in descending order).
myrange3.Sort Key1:=Range("C1"), Order1:=xlDescending, Header:=xlYes

End Sub

Output:

So, the rows are now sorted on column C, i.e., the Location column, in descending order as we wanted and as mentioned in our VBA code.

Sorting on Multiple Columns

Let’s imagine an international bank named “Popular Brand” 😊 has opened its branches in the main cities of different countries. We have listed a few here with other comparative data. We will sort them in different ways to understand how to sort on multiple columns using VBA.

Location	Branch	Rating	No of customers
India	Chennai	4.3	200
Australia	Perth	3.2	340
Australia	Brisbane	4.8	850
India	Bombay	5	150
Australia	Melbourne	3.9	240
India	Delhi	3.3	120
USA	Dallas	5	60
India	Kolkatta	4.4	100
USA	New York	2.3	300
Australia	Sydney	2.95	900
USA	Chicago	4.5	145
Australia	Gold Coast	4.2	280
USA	Seattle	4.9	170

Now I have data until row number 14, including the header. This can be pasted in the top left corner of any open worksheet for testing purposes.

Example 1: Sort on Ascending for a Column of String Values and Descending for a Column of Numeric Values.

In this example, we will sort the created table on two columns. Firstly, we need to group and order the Country column. Next, we will sort the data within each country based on the ratings to award the branch, i.e., column C.

Sub multisort_demo()

' declaration of variables - a range named myrange is declared here.
Dim myrange As Range

' defining the range1 range variable - header is actually included here
Set myrange = Range("A1:D14")

' Sorting the range of data on two columns
With ActiveSheet.Sort
     .SortFields.Add Key:=Range("A1"), Order:=xlAscending ' the countries in order.
     .SortFields.Add Key:=Range("C1"), Order:=xlDescending ' the ratings within each country.
     .SetRange myrange
     .Header = xlYes
     .Apply
End With

End Sub

Output:

Location	Branch	Rating	No of customers
Australia	Sydney	2.95	900
Australia	Perth	3.2	340
Australia	Melbourne	3.9	240
Australia	Gold Coast	4.2	280
Australia	Brisbane	4.8	850
India	Delhi	3.3	120
India	Chennai	4.3	200
India	Kolkatta	4.4	100
India	Bombay	5	150
USA	New York	2.3	300
USA	Chicago	4.5	145
USA	Seattle	4.9	170
USA	Dallas	5	60

In this output, first, we have all countries listed in alphabetical ascending order as desired. But you can notice that the “xlDescending” has not been considered on the numeric column “C” as mentioned in the code. The column has been sorted in ascending order instead of descending order.

Solution to handle this:

We can add another parameter value “SortOn:=xlSortOnValues” after the Order value.

Now the code changes:

Sub multisort_demo()

' declaration of variables - a range named myrange is declared here.
Dim myrange As Range

' defining the range1 range variable - header is actually included here
Set myrange = Range("A1:D14")

' Sorting the range of data on two columns
With ActiveSheet.Sort
     .SortFields.Add Key:=Range("A1"), Order:=xlAscending, SortOn:=xlSortOnValues ' the countries in order.
     .SortFields.Add Key:=Range("C1"), Order:=xlDescending, SortOn:=xlSortOnValues ' the ratings in order high to low
     .SetRange myrange
     .Header = xlYes
     .Apply
End With
End Sub

Output:

Location	Branch	Rating	No of customers
Australia	Brisbane	4.80	850
Australia	Gold Coast	4.20	280
Australia	Melbourne	3.90	240
Australia	Perth	3.20	340
Australia	Sydney	2.95	900
India	Bombay	5.00	150
India	Kolkatta	4.40	100
India	Chennai	4.30	200
India	Delhi	3.30	120
USA	Dallas	5.00	60
USA	Seattle	4.90	170
USA	Chicago	4.50	145
USA	New York	2.30	300

Example 2:  Sort On 3 Columns

In the same example as above, we will add one more column sort to find which cities within each country have the most customers with a good rating.

Sub multisort_demo()

' declaration of variables - a range named myrange is declared here.
Dim myrange As Range

' defining the range1 range variable - header is actually included here
Set myrange = Range("A1:D14")

' Sorting the range of data on two columns
With ActiveSheet.Sort
     .SortFields.Add Key:=Range("A1"), Order:=xlAscending, SortOn:=xlSortOnValues ' the countries in order.
     .SortFields.Add Key:=Range("C1"), Order:=xlDescending, SortOn:=xlSortOnValues ' the ratings in high to low order
     .SortFields.Add Key:=Range("D1"), Order:=xlDescending, SortOn:=xlSortOnValues ' the most number of customers in high to low order
     .SetRange myrange
     .Header = xlYes
     .Apply
End With
End Sub

Output:

Location	Branch	Rating	No of customers
Australia	Brisbane	4.80	850
Australia	Gold Coast	4.20	280
Australia	Melbourne	3.90	240
Australia	Perth	3.20	340
Australia	Sydney	2.95	900
India	Bombay	5.00	150
India	Kolkatta	4.40	100
India	Chennai	4.30	200
India	Delhi	3.30	120
USA	Dallas	5.00	60
USA	Seattle	4.90	170
USA	Chicago	4.50	145
USA	New York	2.30	300

If you observe the output of Example 2 and compare it with the output of Example 1, you will find that the last column (no. of customers) has also been sorted in ascending order.

Conclusion:

Sorting columns the way we want is helpful for report preparation and list preparations in schools, companies, colleges, etc. It makes life simple by easing and speeding the process in no time. The same can be automated using VBA in case it is regular work. Today, I have even inserted it in a regular report wherein I need to sort a sheet into three columns and remove duplicates in 1 column (only after the sort). After reading this article, I hope you will also find good real-life situations where this sort can be done using VBA to ease your work.