VBA Bubble Sort for 2D Arrays

A simple, scalable method for sorting two-dimensional arrays on multiple columns in Excel VBA.

Introduction to Bubble Sort in VBA

Bubble sort is not the fastest sorting method, but it is the simplest to code, understand, and modify. This makes it a great choice for many tasks in VBA, especially when performance is not the absolute top priority.

The scalable solutions below can be expanded to any number of sort columns. These examples assume that Option Base is set to the default of 0, meaning the first column of an array is column zero.

Tip: To sort a column in descending order, simply reverse the greater-than sign (`>`) to a less-than sign (`<`).

Sort a 2D Array on 1 Column

This example sorts a two-dimensional array named `ArrayName` on the first column (column 0). The sort is ascending.

' Sort a 2D array by a single column (ascending)
Dim i As Long, j As Long, y As Long
Dim t As Variant
Dim SortColumn1 As Long
Dim Condition1 As Boolean

SortColumn1 = 0 ' Sort by the first column

For i = LBound(ArrayName, 1) To UBound(ArrayName, 1) - 1
    For j = LBound(ArrayName, 1) To UBound(ArrayName, 1) - 1
        Condition1 = ArrayName(j, SortColumn1) > ArrayName(j + 1, SortColumn1)
        
        If Condition1 Then
            ' Swap entire rows
            For y = LBound(ArrayName, 2) To UBound(ArrayName, 2)
                t = ArrayName(j, y)
                ArrayName(j, y) = ArrayName(j + 1, y)
                ArrayName(j + 1, y) = t
            Next y
        End If
    Next j
Next i
                        

Sort a 2D Array on 2 Columns

This example sorts `ArrayName` on the first column (column 0) and then by the fourth column (column 3) for any ties.

' Sort a 2D array by two columns (both ascending)
' ... (Declare i, j, y, t as above) ...
Dim SortColumn1 As Long, SortColumn2 As Long
Dim Condition1 As Boolean, Condition2 As Boolean

SortColumn1 = 0
SortColumn2 = 3

For i = LBound(ArrayName, 1) To UBound(ArrayName, 1) - 1
    For j = LBound(ArrayName, 1) To UBound(ArrayName, 1) - 1
        ' Primary sort condition
        Condition1 = ArrayName(j, SortColumn1) > ArrayName(j + 1, SortColumn1)
        
        ' Secondary sort condition (if primary columns are equal)
        Condition2 = ArrayName(j, SortColumn1) = ArrayName(j + 1, SortColumn1) And _
                     ArrayName(j, SortColumn2) > ArrayName(j + 1, SortColumn2)

        If Condition1 Or Condition2 Then
            For y = LBound(ArrayName, 2) To UBound(ArrayName, 2)
                t = ArrayName(j, y)
                ArrayName(j, y) = ArrayName(j + 1, y)
                ArrayName(j + 1, y) = t
            Next y
        End If
    Next j
Next i
                        

Sort a 2D Array on 3 Columns

This example extends the logic to three sort levels: column 0, then column 3, then column 5.

' Sort a 2D array by three columns (all ascending)
' ... (Declare i, j, y, t as above) ...
Dim SortColumn1 As Long, SortColumn2 As Long, SortColumn3 As Long
Dim Condition1 As Boolean, Condition2 As Boolean, Condition3 As Boolean

SortColumn1 = 0
SortColumn2 = 3
SortColumn3 = 5

For i = LBound(ArrayName, 1) To UBound(ArrayName, 1) - 1
    For j = LBound(ArrayName, 1) To UBound(ArrayName, 1) - 1
        Condition1 = ArrayName(j, SortColumn1) > ArrayName(j + 1, SortColumn1)
        
        Condition2 = ArrayName(j, SortColumn1) = ArrayName(j + 1, SortColumn1) And _
                     ArrayName(j, SortColumn2) > ArrayName(j + 1, SortColumn2)

        Condition3 = ArrayName(j, SortColumn1) = ArrayName(j + 1, SortColumn1) And _
                     ArrayName(j, SortColumn2) = ArrayName(j + 1, SortColumn2) And _
                     ArrayName(j, SortColumn3) > ArrayName(j + 1, SortColumn3)

        If Condition1 Or Condition2 Or Condition3 Then
            For y = LBound(ArrayName, 2) To UBound(ArrayName, 2)
                t = ArrayName(j, y)
                ArrayName(j, y) = ArrayName(j + 1, y)
                ArrayName(j + 1, y) = t
            Next y
        End If
    Next j
Next i
                        

Sort a 2D Array on 4 Columns

Finally, this example shows a four-level sort. Notice the fourth condition uses `<` for a descending sort on that column, demonstrating how to mix sort orders.

' Sort a 2D array by four columns (mixed order)
' ... (Declare i, j, y, t as above) ...
Dim SortColumn1 As Long, SortColumn2 As Long, SortColumn3 As Long, SortColumn4 As Long
Dim Condition1 As Boolean, Condition2 As Boolean, Condition3 As Boolean, Condition4 As Boolean

SortColumn1 = 0  ' Ascending
SortColumn2 = 3  ' Ascending
SortColumn3 = 5  ' Ascending
SortColumn4 = 1  ' Descending

For i = LBound(ArrayName, 1) To UBound(ArrayName, 1) - 1
    For j = LBound(ArrayName, 1) To UBound(ArrayName, 1) - 1
        Condition1 = ArrayName(j, SortColumn1) > ArrayName(j + 1, SortColumn1)
        
        Condition2 = ArrayName(j, SortColumn1) = ArrayName(j + 1, SortColumn1) And _
                     ArrayName(j, SortColumn2) > ArrayName(j + 1, SortColumn2)
                     
        Condition3 = ArrayName(j, SortColumn1) = ArrayName(j + 1, SortColumn1) And _
                     ArrayName(j, SortColumn2) = ArrayName(j + 1, SortColumn2) And _
                     ArrayName(j, SortColumn3) > ArrayName(j + 1, SortColumn3)
                     
        Condition4 = ArrayName(j, SortColumn1) = ArrayName(j + 1, SortColumn1) And _
                     ArrayName(j, SortColumn2) = ArrayName(j + 1, SortColumn2) And _
                     ArrayName(j, SortColumn3) = ArrayName(j + 1, SortColumn3) And _
                     ArrayName(j, SortColumn4) < ArrayName(j + 1, SortColumn4) ' Note: < for descending

        If Condition1 Or Condition2 Or Condition3 Or Condition4 Then
            For y = LBound(ArrayName, 2) To UBound(ArrayName, 2)
                t = ArrayName(j, y)
                ArrayName(j, y) = ArrayName(j + 1, y)
                ArrayName(j + 1, y) = t
            Next y
        End If
    Next j
Next i
                        