How to Sort an Array in VBA

Previously in VBA it was difficult to Sort an array.

You had to write your own sort function or copy the data to the worksheet and sort it there. Neither method was ideal.

But since Excel 2021 and 365 there is a new Worksheet Sort function which we can use to sort our arrays. In this article I will show you how to use it with plenty of examples.

 

Sample Data for this article

This is the sample data we will be using in this article:

Name	Age	Department
Bob	93	HR
Fiona	31	Finance
Ethan	79	Engineering
Ian	99	Marketing
George	43	Engineering
Charlie	41	HR
Julia	77	Finance
Hannah	89	Marketing
Alice	70	Engineering
Diana	86	HR

 

You can use the following code to test the Sort examples. This code reads the data from Sheet1, sorts the data and writes the sorted data to Sheet2.

 Sub TestSort()

    ' Read data from the sheet1 worksheet
    Dim data As Variant
    data = ThisWorkbook.Worksheets("Sheet1").Range("A2:C11").Value
    
    ' Sort the data
    data = WorksheetFunction.Sort(data)
    
    ' Write the sorted data to the sheet2 worksheet
    ThisWorkbook.Worksheets("Sheet2").Range("A2:C11").Value = data

 End Sub

 

The Worksheet Sort Function

Supported in: Excel for Microsoft 365 Excel for Microsoft 365 for Mac Excel for the web Excel 2024 Excel 2024 for Mac Excel 2021 Excel 2021 for Mac Excel for iPad Excel for iPhone Excel for Android tablets Excel for Android phones Microsoft Office Microsoft365.com

 

The Sort function parameters

array: the array or range to sort

sort_Index: a number indicating the row or column to sort by.

sort_order: a number indicating the desired sort order; 1 for ascending order (default), -1 for descending order.

By_col: a logical value indicating the desired sort direction; FALSE to sort by row (default), TRUE to sort by column.

 

Examples

    ' column 1. Ascending. By row.
    data = WorksheetFunction.Sort(data, 1, 1, False)
    
    ' column 2. Ascending. By row.
    data = WorksheetFunction.Sort(data, 2, 1, False)
    
    ' column 3. Ascending. By row.
    data = WorksheetFunction.Sort(data, 3, 1, False)
    
    ' column 1. Descending. By row.
    data = WorksheetFunction.Sort(data, 1, -1, False)
    
    ' column 2. Descending. By row.
    data = WorksheetFunction.Sort(data, 2, -1, False)
    
    ' column 3. Descending. By row.
    data = WorksheetFunction.Sort(data, 3, -1, False)
    
    ' row 1. Ascending. By column.
    data = WorksheetFunction.Sort(data, 1, 1, True)
    
    ' row 1. Descending. By column.
    data = WorksheetFunction.Sort(data, 1, -1, True)

 

Sorting Arrays for earlier versions of Excel

If the new Sort function is not available in your version of Excel, then you will need to use a function like QuickSort below:

 ' https://excelmacromastery.com/
 Sub QuickSort(arr As Variant, first As Long, last As Long)
  
  Dim vCentreVal As Variant, vTemp As Variant
  
  Dim lTempLow As Long
  Dim lTempHi As Long
  lTempLow = first
  lTempHi = last
  
  vCentreVal = arr((first + last) \ 2)
  Do While lTempLow <= lTempHi
  
    Do While arr(lTempLow) < vCentreVal And lTempLow < last
      lTempLow = lTempLow + 1
    Loop
    
    Do While vCentreVal < arr(lTempHi) And lTempHi > first
      lTempHi = lTempHi - 1
    Loop
    
    If lTempLow <= lTempHi Then
    
        ' Swap values
        vTemp = arr(lTempLow)

        arr(lTempLow) = arr(lTempHi)
        arr(lTempHi) = vTemp
      
        ' Move to next positions
        lTempLow = lTempLow + 1
        lTempHi = lTempHi - 1
      
    End If
    
  Loop
  
  If first < lTempHi Then QuickSort arr, first, lTempHi
  If lTempLow < last Then QuickSort arr, lTempLow, last
  
 End Sub

 

You can use this function like this:

 ' https://excelmacromastery.com/
 Sub TestSort()

    ' Create temp array
    Dim arr() As Variant
    arr = Array("Banana", "Melon", "Peach", "Plum", "Apple")
  
    ' Sort array
    QuickSort arr, LBound(arr), UBound(arr)

    ' Print arr to Immediate Window(Ctrl + G)
    Dim i As Long
    For i = LBound(arr) To UBound(arr)
        Debug.Print arr(i)
    Next i

 End Sub

 

Related topics

Excel VBA Arrays – The Complete Guide