Sorting with VBA

Sorting 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.  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 [D2], xlAscending
End Sub

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.

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; 


will include the header  


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

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 ThisWorkbook.Worksheets
ws.Range("A2", ws.Range("D" & Rows.Count).End(xlUp)).Sort [c2], 1
Next ws
End Sub

Once again ensure Variables are declared and use a For Each Looping constructto iterate through the worksheets and sort each sheet in turn by Column C.

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.

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