[Excel VBA] How to Get Sorted Results of Data or Arrays Using the SORT Function

There is often a need to obtain the sorted results of an Excel data range or a VBA array in memory without modifying the original data.

By calling the SORT function (available in Excel for Microsoft 365 and Excel 2021 or later) from VBA, you can achieve this very simply and quickly.

This article explains how to freely sort cell ranges and VBA arrays using WorksheetFunction.Sort.

[Important] Environments Where SORT Function is Available

To use this technique, your Excel version must support the SORT function (Microsoft 365 or Excel 2021 or later). This code cannot be executed in older versions of Excel.

1. Sorting Cell Ranges (Basic)

First, here is the most basic method to sort a data range on a worksheet.

Code and Explanation

Sub SortRangeWithFunction()

    ' Declare variables
    Dim sourceRange As Range
    Dim sortedResult As Variant

    ' Set the data range to be sorted
    Set sourceRange = ThisWorkbook.Worksheets("DataSheet").Range("C2:C20")

    ' --- 1. Sort the range using the SORT function and store the result in an array ---
    ' If arguments are omitted, it sorts in ascending order
    sortedResult = WorksheetFunction.Sort(sourceRange)

    ' --- 2. Retrieve values from the sorted array and display them ---
    MsgBox "Top 3 results sorted in ascending order:" & vbCrLf & _
           "1st: " & sortedResult(1, 1) & vbCrLf & _
           "2nd: " & sortedResult(2, 1) & vbCrLf & _
           "3rd: " & sortedResult(3, 1), vbInformation, "Sort Result"
           
    '--- 3. (Application) Write the sorted results back to the sheet ---
    ' Expand the range from D2 based on the array size and write all at once
    ' ThisWorkbook.Worksheets("DataSheet").Range("D2").Resize(UBound(sortedResult, 1)).Value = sortedResult

End Sub

• WorksheetFunction.Sort(sourceRange): With this single line, a 2D array containing the values of the specified sourceRange sorted in ascending order is returned.
• sortedResult(1, 1): Accesses the first element (1st row, 1st column) of the array returned by SORT.

2. Sorting VBA Arrays (Advanced)

The SORT function can sort not only cell ranges but also arrays created within VBA. Furthermore, by specifying detailed arguments, you can also sort in descending order.

Code and Explanation

Sub SortVbaArray()

    ' Declare variables
    Dim originalList As Variant
    Dim sortedList As Variant

    ' Create a VBA array to be sorted
    originalList = Array("Apple", "Orange", "Grape", "Strawberry")

    ' --- Sort the array in descending order using the SORT function ---
    sortedList = WorksheetFunction.Sort(originalList, , -1) ' Specify -1 for the 3rd argument

    ' Join the array elements with commas and display them
    MsgBox "Original Array: " & Join(originalList, ", ") & vbCrLf & vbCrLf & _
           "Result sorted in descending order: " & Join(sortedList, ", "), vbInformation, "Array Sorting"

End Sub

Main Arguments of the SORT Function

The SORT function has optional arguments for finer control: SORT(array, [sort_index], [sort_order], [by_col]).

• array (Required): The cell range or array to sort.
• [sort_index] (Optional): Specifies the column number to sort by (e.g., enter 2 to sort by the second column).
• [sort_order] (Optional): Specifies the sort order.
  • 1: Ascending (A to Z, Smallest to Largest) (Default)
  • -1: Descending (Z to A, Largest to Smallest)
• [by_col] (Optional): Specifies the direction of sorting.
  • False: Sort by row (Default)
  • True: Sort by column

Summary

In modern Excel environments where the SORT function is available, sorting in VBA becomes very easy.

• By using WorksheetFunction.Sort(target), you can get the sorted results as an array without changing the original data at all.
• You can directly sort not only cell ranges but also VBA arrays.
• By specifying optional arguments, complex sorting such as descending order can be achieved in a single line.

This function is extremely effective in situations where you need a sorted list temporarily without physically rearranging the data.