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.

Example 1:

Consider the range below. Assume we want to sort the range in ascending order based on column A:
Range
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:
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.

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:
Result

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.

Leave a Reply

Your email address will not be published. Required fields are marked *