Sorting with Excel VBA - A Complete Guide

Sorting in Excel is one of those manual tasks that are often required when manipulating data.  When creating broader procedures you may need to sort Excel data in either ascending or descending order with VBA. 

Take the following data as an example. You can sort this table by using the sort icons, or perform more complex sorts using the dialog box. For this example we will use the sort icon.

Sorting in Excel

Let’s say we want to sort the above table by column A. We can use the sort icon.

Sort Excel VBA

The above is suggesting we sort the data A to Z or in ascending order sort. Or use Z to A to sort in descending order.

Sorting in Excel VBA

The above table is what the data set will look like after the sort.


Sorting with VBA

To do the same process in VBA - here is the syntax and a quick trick regarding where to start the sort from.

  1. Your Range

  2. Starting Cell/column to sort from

  3. Ascending or Descending Order.

In VBA the sorting procedure would look like this:

[A2:D10].Sort A2, xlAscending

  1. Where the Range = A2:A10.

  2. Starting cell = A2 (trick for new players always leave the headings out, simplifies the VBA code).

  3. Ascending order = xlAscending (for descending order xlDescending).

The process in VBA happens instantly and is a very simple bit of code to apply. It is one of the 10 most important coding syntax to remember when using the VBA language in my opinion.


Sorting Dynamically with VBA

To make the code a little more dynamic you might want to add a dynamic range inside VBA, this ensures that the sort can happen as the range grows and your code does not have to change provided the columns don’t grow.

The Excel VBA code to produce a simple sort by ascending order is quite straight forward and is as follows;

Sub SortIt1() 'Excel VBA for a sort (ascending).
Range("A2", Range("D" & Rows.Count).End(xlUp)).Sort [A2], xlAscending
End Sub

Another way to write the above would be to create a dynamic range based on rows and columns. This time we will sort the range in descending order for a change from the above sort. The following VBA code has the advantage of being fully dynamic for a sort assuming a tabular data set (rows and columns - no breaks).

Sub SortIt1() 'Excel VBA for a sort (Descending).
[A1].CurrentRegion.Offset(1).Sort [A2], xlDescending
End Sub

The following brief video takes you through some sorting examples. The Excel file below is the start file.

Sorting Data VBA.xlsm

 
 

It is important to note that the headers start in Row 1 so the sorting starts from Row 2.  If you have to start in Row 1 and you want the code to not sort the headers then the following considers the headers in place.

Option Explicit
Sub SortIt2() 'Excel VBA for a sort headers
Range("A1", Range("D" & Rows.Count).End(xlUp)).Sort [D2], xlAscending, Header:=xlYes
End Sub

The above will sort data from A2 to the last row in Column D and in Ascending order.  The indexing for Ascending is number 1 so you can replace xlAscending with 1 and get the same result.  Conversely for descending order the code is virtually identical.  While the following; 

Header:=xlYes

will include the header  

Header:=xlNo

will work if you don’t have a header.  But as mentioned above if you start with the second row there is no need for these xlYes of XlNo lines at all.  The following is the same Excel VBA code for descending order.

Sub SortIt3() 'Excel VBA for a sort (descending).
Range("A2", Range("D" & Rows.Count).End(xlUp)).Sort [D2], xlDescending
End Sub

For descending order this number the indexing can also be a 2.  But for readability the above suffices. I tend to use 1 and 2 at the end when there is a long line of code and I want to save some space.

Range("A2", Range("D" & Rows.Count).End(xlUp)).Sort [D2], 2       'Where 2=Descending

Sorting Across Multiple Sheets

As with most code it is often more useful if you see it as part of a larger procedure.  Assuming all the sheets are the same and you want to sort all of the sheets in Ascending order.  Something like the following will sort all of the sheets in the workbook into Ascending order based on the above formatting pattern.

Option Explicit
Sub SortWS() 'Excel VBA to Sort All Sheets in Workbook in Ascending Order
Dim ws As Worksheet

For Each ws In Sheets
ws.[A1].CurrentRegion.Offset(1).Sort [C2], xlAscending
Next ws
End Sub

Once again ensure variables are declared and use a For Each looping construct to iterate through the worksheets and sort each sheet in turn by column C. The above is dynamic for both rows and columns, so provided column C is the column of choice then the sort can take place as the data grows both vertically and horizontally. This makes the sort dynamic.

Sort by Colour VBA

In more recent versions of Excel the ability to sort by colour has been included.  This can be very helpful if colour coding is part of a column of data. The following sorts all of the columns by the interior colour Red. Sorting by the Red Green Blue (RGB) and Red is RGB( 255, 0, 0).

To Sort all of the sheets in your workbook by the colour Red then the following will VBA procedure will perform this task.

Option Explicit
Sub SortSheetsColour() 'Excel VBA to sort by colour.
Dim ws As Worksheet
Dim i As Integer
Dim lw As Long

For Each ws In Sheets
lw=ws.Range("A" & Rows.Count).End(xlUp).Row
For i = 1 To ws.Cells(1, Columns.Count).End(xlToLeft).Column
ws.Sort.SortFields.Clear
ws.Sort.SortFields.Add(Range(ws.Cells(1, i), ws.Cells(lw, i)), 1, 2, 1).SortOnValue.Color=RGB(255, 0, 0)
ws.Sort.SetRange ws.Range(ws.Cells(1, i), ws.Cells(lw, i))
ws.Sort.Apply
Next i
Next ws
End Sub

The attached Excel file has the above VBA examples inside.  To sort on more than one level (multiple level sorting using Excel VBA.  Have a look at this article on Excel VBA Multi Level Sort.