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!
Hello, j is not defined: t = YourArrayName (j, y)
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
How should I dim t? I get Type Mismatch.
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———————————————————–