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. samsi

Hello, j is not defined: t = YourArrayName (j, y)

2. 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

3. Mike

How should I dim t? I get Type Mismatch.

4. 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———————————————————–