How to Sort an Array Using Excel VBA (4 Different Examples)

Sorting arrays in Excel VBA is useful when working with data that’s not stored directly on the worksheet, such as temporary lists, lookup tables, or custom datasets. Arrays let you handle large amounts of data in memory, and sorting them makes your code more powerful and efficient.

In this article, you will learn three practical VBA methods to sort arrays. We’ll cover how to sort a 2D array using WorksheetFunction, implement a custom bubble sort for more control, and sort values while pulling data from a range into an array.

Sub Sort2DArrayByColumn()
    Dim data As Variant
    Dim i As Long, j As Long
    Dim temp As Variant
    Dim numRows As Long, numCols As Long
    ' Load 2D array with product data: {Product, Category, Quantity}
    data = Array( _
        Array("A1001", "Tools", 10), _
        Array("B2002", "Office", 15), _
        Array("C3003", "Kitchen", 8), _
        Array("D4004", "Tools", 20), _
        Array("E5005", "Office", 12) _
    )
    numRows = UBound(data)
    numCols = UBound(data(0))
    ' Sort by 3rd column (Quantity) in ascending order
    For i = 0 To numRows - 1
        For j = i + 1 To numRows
            If data(i)(2) > data(j)(2) Then
                temp = data(i)
                data(i) = data(j)
                data(j) = temp
            End If
        Next j
    Next i
    ' Output sorted array to Immediate Window
    For i = 0 To numRows
        Debug.Print data(i)(0) & vbTab & data(i)(1) & vbTab & data(i)(2)
    Next i
    MsgBox "2D array sorted by Quantity column.", vbInformation
End Sub

When you’re working with a small set of values inside an array, like a list of prices, you might want to sort them without writing the values into cells first. Bubble Sort is a beginner-friendly sorting technique that uses simple nested loops to organize items in ascending or descending order. While not the fastest, it’s perfect for learning and small-scale data.

In this method, we’ll load a 1D array of prices, sort it in ascending order using Bubble Sort, and display the sorted values in a message box.

Steps:

➤ Open Excel and press  Alt  +  F11  to launch the VBA editor
➤ Go to Insert >> Module

➤ Paste this code:

Sub SortPriceArray()
    Dim i As Long, j As Long
    Dim Temp As Variant
    Dim prices() As Variant
    ' Load the array with price values
    prices = Array(150, 40, 25, 950, 65)
    ' Bubble Sort: Ascending Order
    For i = LBound(prices) To UBound(prices) - 1
        For j = i + 1 To UBound(prices)
            If prices(i) > prices(j) Then
                Temp = prices(j)
                prices(j) = prices(i)
                prices(i) = Temp
            End If
        Next j
    Next i
    ' Output the sorted prices as a message box
    MsgBox Join(prices, vbCrLf)
End Sub

➤ Return to Excel, press  Alt  +  F8  , select SortPriceArray, and click Run

➤ A message box will appear showing the sorted prices in ascending order
➤ You can also sort the array in the other direction, eg: highest to lowest by changing this line of code-

If prices(i) > prices(j) Then

to this line of code-

If prices(i) < prices(j) Then

➤ You would then get the following message box:

When you need to sort large datasets inside an array efficiently, QuickSort is a powerful and fast recursive sorting algorithm. Unlike Bubble Sort, which compares elements one by one, QuickSort divides the array into partitions and sorts them recursively, making it much faster for larger lists.

In this method, we’ll define a 1D array of numbers, apply QuickSort to arrange them in ascending order, and display the sorted result in a message box.

Steps:

➤ Open Excel and press  Alt  +  F11  to launch the VBA editor
➤ Go to Insert >> Module and paste this code:

Sub QuickSortArray()
    Dim prices() As Variant
    Dim i As Long
    ' Load the array with price values
    prices = Array(150, 40, 25, 950, 65)
    ' Apply QuickSort
    Call QuickSort(prices, LBound(prices), UBound(prices))
    ' Output sorted prices in a message box
    MsgBox Join(prices, vbCrLf)
End Sub
Sub QuickSort(arr() As Variant, first As Long, last As Long)
    Dim low As Long, high As Long
    Dim mid As Variant, temp As Variant
    low = first
    high = last
    mid = arr((first + last) \ 2)
    Do While low <= high
        Do While arr(low) < mid
            low = low + 1
        Loop
        Do While arr(high) > mid
            high = high - 1
        Loop
        If low <= high Then
            temp = arr(low)
            arr(low) = arr(high)
            arr(high) = temp
            low = low + 1
            high = high - 1
        End If
    Loop
    If first < high Then QuickSort arr, first, high
    If low < last Then QuickSort arr, low, last
End Sub

➤ Press  Alt  +  F8  , select QuickSortArray, and click Run

➤ A message box will appear showing the sorted prices in ascending order
➤ You can also sort the array in the other direction, eg: highest to lowest, by changing this line of code-

Do While arr(low) < mid

to this line of code-

Do While arr(low) > mid

➤ You would then get the following message box:

When working with tabular data stored in a 2D array, like product info or transaction records, you may want to sort it by a specific column, such as Quantity or Score. This method shows how to sort a two-dimensional array in VBA based on one of its columns (e.g., the third column), just like sorting a table by a field.

In this method, we’ll define a 2D array with product data, sort it by the Quantity column (column 3), and display the sorted result in the Immediate Window.

Steps:

➤ Open Excel and press  Alt  +  F11  to open the VBA editor
➤ Go to Insert >> Module and paste this code:

Sub Sort2DArrayByColumn()
    Dim data As Variant
    Dim i As Long, j As Long
    Dim temp As Variant
    Dim numRows As Long, numCols As Long
    ' Load 2D array with product data: {Product, Category, Quantity}
    data = Array( _
        Array("A1001", "Tools", 10), _
        Array("B2002", "Office", 15), _
        Array("C3003", "Kitchen", 8), _
        Array("D4004", "Tools", 20), _
        Array("E5005", "Office", 12) _
    )
    numRows = UBound(data)
    numCols = UBound(data(0))
    ' Sort by 3rd column (Quantity) in ascending order
    For i = 0 To numRows - 1
        For j = i + 1 To numRows
            If data(i)(2) > data(j)(2) Then
                temp = data(i)
                data(i) = data(j)
                data(j) = temp
            End If
        Next j
    Next i
    ' Output sorted array to Immediate Window
    For i = 0 To numRows
        Debug.Print data(i)(0) & vbTab & data(i)(1) & vbTab & data(i)(2)
    Next i
    MsgBox "2D array sorted by Quantity column.", vbInformation
End Sub

➤ Press  Alt  +  F8  , select Sort2DArrayByColumn, and click Run

➤ The Immediate Window will show the product data sorted by Quantity in ascending order

Sorting a 2D array by each of its columns can be helpful when analyzing small grid-like datasets directly in memory. This method demonstrates how to sort a 5×3 multidimensional array using nested loops. Instead of sorting by just one column, this approach sorts each column independently, from left to right, with a simple comparison technique.

In this method, we’ll create a 5-row by 3-column 2D array, sort each column in ascending order, and display the results in the Immediate Window.

Steps:

➤ Open Excel and press  Alt  +  F11  to open the VBA editor
➤ Go to Insert >> Module and paste this code:

Sub SortMultiDimArray()
    Dim arr(1 To 5, 1 To 3) As Variant
    Dim i As Long, j As Long, k As Long
    Dim temp As Variant
    ' Load values into the 2D array (5 rows, 3 columns)
    arr(1, 1) = 5: arr(1, 2) = 3: arr(1, 3) = 7
    arr(2, 1) = 1: arr(2, 2) = 9: arr(2, 3) = 2
    arr(3, 1) = 6: arr(3, 2) = 8: arr(3, 3) = 4
    arr(4, 1) = 2: arr(4, 2) = 4: arr(4, 3) = 9
    arr(5, 1) = 3: arr(5, 2) = 1: arr(5, 3) = 8
    ' Sort each column independently (ascending order)
    For j = 1 To 3
        For i = 1 To 4
            For k = i + 1 To 5
                If arr(i, j) > arr(k, j) Then
                    temp = arr(i, j)
                    arr(i, j) = arr(k, j)
                    arr(k, j) = temp
                End If
            Next k
        Next i
    Next j
    ' Print the sorted array to Immediate Window
    For i = 1 To 5
        For j = 1 To 3
            Debug.Print arr(i, j);
        Next j
        Debug.Print
    Next i
    MsgBox "Multidimensional array sorted by each column.", vbInformation
End Sub

➤ Press  Alt  +  F8  , select SortMultiDimArray, and click Run.

Frequently Asked Questions

Can you use .Sort or .Reverse on VBA arrays directly?

No, those methods are available only in VB.NET. In VBA you must use Application.WorksheetFunction.Sort, ArrayList, or implement custom algorithms like Bubble or QuickSort.

What is the fastest way to sort a VBA array?

The quickest approach is to transfer the array to a worksheet, use Excel’s native Sort, and read it back. It beats manual Bubble Sort for large datasets.

Does Excel 365 support sorting arrays natively in VBA?

Yes, Excel 365 dynamic array-enabled versions allow Application.WorksheetFunction.Sort(yourArray), which returns a sorted variant without needing manual loops.

How can I sort a 2D array by one column?

You can adapt QuickSort to 2D arrays by passing the column index as a parameter. A pivot-based QuickSort sub sorts rows based on that specific column

Wrapping Up

Sorting arrays in Excel VBA gives you full control over how your data is organized, without relying on worksheet cells. Whether you’re working with a simple 1D list or a 2D table-like structure, VBA lets you implement everything from beginner-friendly Bubble Sort to efficient QuickSort techniques. By mastering these array-based sorting methods, you can streamline your automation tasks and handle data with greater speed and precision, all directly in memory.