How to Sort a 2 Dimensional Array on two Columns in Excel VBA

Sorting a two dimensional array in Excel VBA based on two columns, can be completed fairly easily using the code below.

The code assumes that your array starts at 0. So, in other words you have not declared Option Base = 1.

Where it says Sort1=1 this is referring to the array and specifying that you wish to sort the array by the second column which is 1.

Where it says Sort2=7 this is referring to the array and specifying that you wish to sort the array by the eighth column, which is 7.

Remember, in Excel arrays the rows and columns start at 0, unless at the very top of the code you write Option Base 1. Having this at the top will force an array to start at 1 and not 0. So that when you reference an array element, you call it by its actual location, for example (1,2). This could be (row 1, column 2) depending on how your array is set up. Whereas, without the Option Base 1 at the top you would reference the previous element as (0,1).

When you see > this is what specifys the ascending sort. To sort descending simply swap > with <. For example:

Condition1= YourArrayName (Y, Sort1) > YourArrayName (Y + 1, Sort1) – Is ascending

Condition1= YourArrayName (Y, Sort1) < YourArrayName (Y + 1, Sort1) – Is descending

The LBound(YourArrayName,1) specifies the lowest possible value in the array. In this case it would be zero.

The UBound(YourArrayName,1) specifies the highest possible value in the array. So this would be the number of the last element in the array.

Sort1=1
Sort2=7
For X = LBound(YourArrayName, 1) To UBound(YourArrayName, 1) - 1
For Y = LBound(YourArrayName, 1) To UBound(YourArrayName, 1) - 1
Condition1= YourArrayName (Y, Sort1) > YourArrayName (Y + 1, Sort1)
Condition2= YourArrayName (Y, Sort1) = YourArrayName (Y + 1, Sort1) And _
YourArrayName (Y, Sort2) > YourArrayName (Y + 1, Sort2)

If Condition1 or Condition2 then
For Z = LBound(YourArrayName, 2) To UBound(YourArrayName, 2)
t = YourArrayName (j, y)
YourArrayName (Y, Z) = YourArrayName (Y + 1, Z)
YourArrayName (Y + 1, Z) = t
Next Z
End If
Next Y
Next X

I hope you found this post useful!

4 thoughts on “How to Sort a 2 Dimensional Array on two Columns in Excel VBA

  1. Laszlo Kozma

    Hello, Samsi has right. 🙂
    This is a bubble sort and the correct code is:
    t = YourArrayName (Y, Z)
    t is a temporary array, you can store the actual values in it while this row is overwrited with the next row

  2. Romain

    Hello,

    Thanks OP for the logic.
    I added the DIM and made it a function if anybody comes here later.

    ———————————————————-start of code————————————————–
    Sub S_Sort2DimArrOn2Col(ByRef SortArray As Variant, ByVal lSortCol1 As Long, ByVal lSortCol2 As Long)

    Dim X, Y, Z As Long
    Dim Condition1, Condition2 As Boolean
    Dim tmpStr As String

    For X = LBound(SortArray, 1) To UBound(SortArray, 1) – 1

    For Y = LBound(SortArray, 1) To UBound(SortArray, 1) – 1

    Condition1 = SortArray(Y, lSortCol1) > SortArray(Y + 1, lSortCol1)
    Condition2 = SortArray(Y, lSortCol1) = SortArray(Y + 1, lSortCol1) And _
    SortArray(Y, lSortCol2) > SortArray(Y + 1, lSortCol2)

    If Condition1 Or Condition2 Then
    For Z = LBound(SortArray, 2) To UBound(SortArray, 2)
    tmpStr = SortArray(Y, Z)
    SortArray(Y, Z) = SortArray(Y + 1, Z)
    SortArray(Y + 1, Z) = tmpStr
    Next Z
    End If

    Next Y

    Next X

    End Sub
    ————————————————————end of code———————————————————–

    and if you want to test it, here is a short sub to call the sort sub

    ————————————————————start of code———————————————————–
    Sub test_S_Sort2DimArrOn2Col()

    Dim myArray() As Variant

    ReDim myArray(5, 3)

    myArray(1, 1) = “9999”
    myArray(2, 1) = “7777”
    myArray(3, 1) = “9999”
    myArray(4, 1) = “7777”
    myArray(5, 1) = “9999”

    myArray(1, 2) = “-1-”
    myArray(2, 2) = “-2-”
    myArray(3, 2) = “-3-”
    myArray(4, 2) = “-4-”
    myArray(5, 2) = “-5-”

    myArray(1, 3) = “1”
    myArray(2, 3) = “2”
    myArray(3, 3) = “2”
    myArray(4, 3) = “2”
    myArray(5, 3) = “1”

    Call S_Sort2DimArrOn2Col(myArray, 1, 3)

    End Sub
    ————————————————————end of code———————————————————–

Leave a Reply

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