Sort A Range of Cells in Excel Using VBA
In this article I will explain how you can use VBA for excel to sort a range of cells.
Contents
Example 1:
Consider the range below. Assume we want to sort the range in ascending order based on column A:
The following code will filter based on column A in ascending order:
Sheet1.Sort.SortFields.Clear Call Sheet1.Sort.SortFields.Add(Sheet1.Columns(1), , xlAscending) Call Sheet1.Sort.SetRange(Sheet1.Range("A2:C1048576")) Sheet1.Sort.Apply
Line 1 clears all previous sort rules. Line 2 states that we will sorting based on column A in ascending order. Line 3 specifies the sort range. Note that in this example we only have data upto row 6, but in another example there might be data upto row 10, and so on. In order to cover all examples I specified the range to extend from row 2 all the way down to the last row (which is 1048576 in .xlsx files). Line 3 causes the sort command to execute.
Result:
Example 2, Dynamic Column Indices:
In example 1 we knew that we are sorting the first 3 columns based on the first column before runtime. But what if we didn’t know before hand what range we will be sorting or what column we will be sorting by? In this case you can use the function below. The function receives as input 3 parameters:
- Column2Sortby: This is the numeric index of the column that the range will be sorted by.
- ColumnStartSort: This is the first column in the range that will be sorted.
- ColumnEndSort: This is the end column in the range that will be sorted.
Private Sub Example2(ByVal Column2Sortby As Integer, ByVal ColumnStart As Integer, ByVal ColumnEnd As Integer) Sheet1.Sort.SortFields.Clear Call Sheet1.Sort.SortFields.Add(Sheet1.Columns(Column2Sortby), , xlAscending) Call Sheet1.Sort.SetRange(Sheet1.Range(Sheet1.Cells(2, ColumnStart), Sheet1.Cells(1048576, ColumnEnd))) Sheet1.Sort.Apply End Sub
Calling context:
Call Example2(1, 1, 3)
The example used as the calling context above will have the same result as example 1.
We’ve got a whole article about how to do this, over here.
Example 3, Sort Multiple Columns:
Lets say you want to apply a sort based on multiple columns and you don’t know the columns before running the code, you could use the function below. The function has 3 input parameters:
- arrColumns: An array with the index of the range will be sorted by.
- ColumnStartSort: This is the first column in the range that will be sorted.
- ColumnEndSort: This is the end column in the range that will be sorted.
Private Sub Example3(ByRef arrColumns() As Variant, ByVal ColumnStart As Integer, ByVal ColumnEnd As Integer) Dim i As Integer Sheet1.Sort.SortFields.Clear 'columns to sort by For i = LBound(arrColumns) To UBound(arrColumns) Call Sheet1.Sort.SortFields.Add(Sheet1.Columns(arrColumns(i)), , xlAscending) Next i Call Sheet1.Sort.SetRange(Sheet1.Range(Sheet1.Cells(2, ColumnStart), Sheet1.Cells(1048576, ColumnEnd))) Sheet1.Sort.Apply End Sub
Calling context:
Dim arrColumns(0 To 2) arrColumns(0) = 3 arrColumns(1) = 2 arrColumns(2) = 1 Call Example3(arrColumns, 1, 3)
The calling context results in the following sort:
Example 4, Only Sort Rows With Data:
In the previous examples the sort function is applied to all rows in the column. Sometimes you just want to sort certain rows. Normally you would want the sort to be applied to only the cells with data in them. I will be using the function used in example 2 here. The new function has the following parameters:
- EndRow: The last row with data to sort.
- Column2Sortby: This is the numeric index of the column that the range will be sorted by.
- ColumnStartSort: This is the first column in the range that will be sorted.
- ColumnEndSort: This is the end column in the range that will be sorted.
Private Sub Example4(ByVal EndRow As long, ByVal Column2Sortby As Integer, _ ByVal ColumnStart As Integer, ByVal ColumnEnd As Integer) Sheet1.Sort.SortFields.Clear Call Sheet1.Sort.SortFields.Add(Sheet1.Columns(Column2Sortby), , xlAscending) Call Sheet1.Sort.SetRange(Sheet1.Range(Sheet1.Cells(2, ColumnStart), Sheet1.Cells(EndRow, ColumnEnd))) Sheet1.Sort.Apply End Sub
As you can see above a new parameter was added to the function. The EndRow parameter is the last row that is part of the range to sort. You can obtain the last row with a value in it using the function below:
Private Function LastRow(ByVal intColumn As Integer) As Long Dim i As Long Dim flag As Boolean flag = True While flag = True If Sheet1.Cells(i, 1) <> "" Then i = i + 1 Else flag = False End If LastRow = i - 1 End Function
The function receives as input the index of a column and returns the last row with data in it. The calling context would look something like this:
Dim intRows As Long intRows = LastRow(1) Call Example4(intRows, 1, 1, 3)
The code above will sort the range “A2:C6” based on column A.
Example 5, Referring Columns by Their Character Representation
In all the examples in this article the columns were reference using their numeric index. In this example I will explain how you can reference them using there character representation. The function receives the following input parameters:
- strColumn2SortBy: Character representation of the column to sort by.
- strColumnStart: Character representation of the first column in the range to sort.
- strColumnEnd: Character representation of the last column in the range to sort.
Private Sub Example5(ByVal strColumn2SortBy As String, ByVal strColumnStart As String, ByVal strColumnEnd As String) Sheet1.Sort.SortFields.Clear Call Sheet1.Sort.SortFields.Add(Sheet1.Columns(strColumn2SortBy & ":" & strColumn2SortBy), , xlAscending) Call Sheet1.Sort.SetRange(Sheet1.Range(strColumnStart & "2:" & strColumnEnd & "1048576")) Sheet1.Sort.Apply End Sub
Calling Context:
Call Example5("A", "A", "C")
The result will be the same as example 1.
Example 6: Auto Sort a sheet every 5 min.
Suppose that your Excel has constantly changing data, say tickers of a stock market for example. In such cases, you would need to sort the sheet periodically. You can use the following sorting function in the module section. The sort function below also demonstrates various parameters / options available for sorting.
Function sortSheet() Dim lastRow As Integer Dim sortCol As String Dim timeString As String Dim alertTime As Date lastRow = Sheet1.UsedRange.Rows.Count timeString = "00:05:00" Application.ScreenUpdating = False Sheet1.Sort.SortFields.Add Key:=Range("A1:A" & lastRow), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal With Sheet1.Sort .SetRange Range("A1:G" & lastRow) .Header = xlYes .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With Sheet1.Sort.SortFields.Clear Application.ScreenUpdating = True alertTime = Now + TimeValue(timeString) Application.OnTime alertTime, "sortSheet" End Function
First, we add a sort field and specify the parameters for the SortFields.Add method.
– SortOn can be – SortOnCellColor, SortOnFontColor, SortOnIcon and SortOnValues
– Order can be xlDescending and xlAscending
– DataOption can be xlSortNormal (default. Sorts numeric and text data separately) or xlSortTextAsNumbers (Treat text as numeric data for the sort)
Next, we perform the actual sort (using .sort) after specify the options for the sorting:
– .header: whether the range specified contains a header or no (i.e. whether to ignore the first row or not while sorting)
– .MatchCase: whether the sorting is case sensitive
– .Orientation can be xlLeftToRight or xlTopToBottom
– .SortMethod can be xlPinYin or xlStroke
It is a good practice to clear the sort fields after the sorting is done. Especially when you are going to run the function periodically.
You can then call this function when the workbook is opened
Private Sub Workbook_Open() Call sortSheet End Sub
See also: The Application.Ontime Method
Example 7: Auto Sort a sheet whenever the worksheet changes.
In the above example, the stock tickers change every second and hence, we cannot sort the worksheet every time a value changes. However, in some other cases, where the change is not so frequent, we can execute the code each time there is a change in that column on which the sorting is done.
Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column = 1 Then Application.EnableEvents = False Columns("A").Sort Key1:=Range("A1"), Order1:=xlDescending, _ Header:=xlYes, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom Application.EnableEvents = True End If End Sub
Example 8: Sorting columns (left to right) instead of rows (top to bottom)
Sub SortLeftToRight() Range("A1:Z5").Sort Key1:=Range("A1:Z1"), Order1:=xlAscending, Orientation:=xlLeftToRight Range("A8:Z11").Sort Key1:=Range("A8:Z8"), Order1:=xlAscending, Orientation:=xlLeftToRight End Sub
Here, there are two separate tables that are sorted. First the columns A to Z are sorted based on the values in row 1. The sort is extended to first five rows
Similarly, second sorting is done based on the values in row 8 and is done on columns A to Z and rows 8 to 11.
See also: How to Unsort in Excel
One thought on “Sort A Range of Cells in Excel Using VBA”