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;
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.
Sub SortIt2() 'Excel VBA for a sort headers
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.
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.
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.
Sub SortWS() 'Excel VBA to Sort All Sheets in Workbook in Ascending Order
Dim ws As Worksheet
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.
Sub SortSheetsColour() 'Excel VBA to sort by colour.
Dim ws As Worksheet
Dim i As Integer
Dim lw As Long
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))
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.