How do a sort a multidimensional array(1000,2) in Excel-VBA?

[DJMF]

I have a 2 column array of numerical entries in VBA that I want to sort
lowest to highest by the first column (i.e. I still want to retain the links
between the values in the first and second columns, just like sorting a pair
of columns in a worksheet). I'd prefer not to have to move the numbers into
a worksheet to do this.

 

[Per Jessen]

I have a 2 column array of numerical entries in VBA that I want to sort
lowest to highest by the first column (i.e. I still want to retain the
links
between the values in the first and second columns, just like sorting a
pair
of columns in a worksheet). I'd prefer not to have to move the numbers
into
a worksheet to do this.

Hi

Try this code. Fit the array size to your needs.

Option Explicit

Option Base 1
Dim MyArray(10, 2)
Dim MyResultArray(10, 2)
Dim Largest As Double
Dim Smallest As Variant
Dim c As Integer, x As Integer
Dim Counter As Integer
Dim Pointer As Single
Dim CountTo As Double

Sub SortArray()
' Assign data to aray
For c = 1 To 10 ' Assign array
MyArray(c, 1) = Cells(c, 1).Value
MyArray(c, 2) = Cells(c, 2).Value
Next

CountTo = UBound(MyArray, 1)

' Sort Data in array
For c = 1 To CountTo
If MyArray(c, 1) > Largest Then Largest = MyArray(c, 1) ' Largest value
in aray
Next
Smallest = Largest
For c = 1 To CountTo
If MyArray(c, 1) < Smallest Then Smallest = MyArray(c, 1) ' Lowest
value in aray
Next
Pointer = 1
Counter = Smallest
Do Until MyResultArray(CountTo, 1) = Largest
For c = 1 To UBound(MyArray, 1)
If MyArray(c, 1) = Smallest Then
MyResultArray(Pointer, 1) = MyArray(c, 1)
MyResultArray(Pointer, 2) = MyArray(c, 2)
MyArray(c, 1) = True
Pointer = Pointer + 1
Smallest = Largest
For x = 1 To CountTo
If MyArray(x, 1) <> True Then
If MyArray(x, 1) < Smallest Then Smallest = MyArray(x,
1)
End If
Next
End If
Next
Loop
End Sub

Regards,

Per

 

[Ivan]

I am afraid of you have to find a code for sort (for example "bubble" sort),
but you can do also like this: in VBA code put the array in the first two
column of one new empty worksheet, then do sort in the worksheet and after
that take the contents of the worksheet column back into the VBA array.

Ivan