In this article I will explain how you can use VBA for excel to sort a range of cells.

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