Active Range to an array, sort the array and display content.

[jbfuentes]

Please help! I want to put a two dimensional selected range values into a single column array. Then sort the array values in ascending order. Then display the content of the sorted array.
This is VBA and Excel. Thanks!

Sub SortArray()

Dim rangearray() As Variant
Dim SourceRange As Range
Set SourceRange = Selection.CurrentRegion
rangearray = SourceRange.Value

'Call function to sort rangearray
Call Sortarray(rangearray)

End Sub

Sub Sortarray(MyArray() As Variant)

Dim First As Integer
Dim Last As Integer
Dim i As Integer
Dim j As Integer
Dim Temp As String
Dim list As String

First = LBound(MyArray)
Last = UBound(MyArray)
For i = First To Last - 1
For j = i + 1 To Last
If MyArray(i) > MyArray(j) Then
Temp = MyArray(j)
MyArray(j) = MyArray(i)
MyArray(i) = Temp
End If
Next j
Next i

For i = 1 To UBound(MyArray)
list = list & vbCrLf & MyArray(i)
Next
MsgBox list

End Sub

 

[Fluff]

Hi & welcome to MrExcel
Try this

Sub Sortarray()

Dim rangearray() As Variant
Dim SourceRange As Range
Dim Cl As Range
Dim Cnt As Long

Set SourceRange = Selection.CurrentRegion
For Each Cl In SourceRange
    Cnt = Cnt + 1
    ReDim Preserve rangearray(1 To Cnt)
    rangearray(Cnt) = Cl
Next Cl
'Call function to sort rangearray
Call Sortarray2(rangearray)

End Sub

Sub Sortarray2(MyArray() As Variant)

Dim First As Integer
Dim Last As Integer
Dim i As Integer
Dim j As Integer
Dim Temp As String
Dim list As String

First = LBound(MyArray)
Last = UBound(MyArray)
For i = First To Last - 1
For j = i + 1 To Last
If MyArray(i) > MyArray(j) Then
Temp = MyArray(j)
MyArray(j) = MyArray(i)
MyArray(i) = Temp
End If
Next j
Next i

For i = 1 To UBound(MyArray)
list = list & vbCrLf & MyArray(i)
Next
MsgBox list

End Sub

 

[MickG]

Perhaps this, Initial array in columns "A & B"

[COLOR=navy]Sub[/COLOR] Sortarray()
[COLOR=navy]Dim[/COLOR] n [COLOR=navy]As[/COLOR] [COLOR=navy]Long,[/COLOR] Ac [COLOR=navy]As[/COLOR] [COLOR=navy]Long,[/COLOR] c [COLOR=navy]As[/COLOR] [COLOR=navy]Long[/COLOR]
[COLOR=navy]Dim[/COLOR] rangearray() [COLOR=navy]As[/COLOR] Variant
[COLOR=navy]Dim[/COLOR] SourceRange [COLOR=navy]As[/COLOR] Range
[COLOR=navy]Set[/COLOR] SourceRange = Selection.CurrentRegion
rangearray = SourceRange.Value
ReDim Ray(1 To UBound(rangearray) * 2)
[COLOR=navy]For[/COLOR] Ac = 1 To 2
    [COLOR=navy]For[/COLOR] n = 1 To UBound(rangearray, 1)
        c = c + 1
        Ray(c) = rangearray(n, Ac)
    [COLOR=navy]Next[/COLOR] n
[COLOR=navy]Next[/COLOR] Ac
Call Sortarray(Ray)
 [COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]
 [COLOR=navy]Sub[/COLOR] Sortarray(MyArray [COLOR=navy]As[/COLOR] Variant)
 [COLOR=navy]Dim[/COLOR] First [COLOR=navy]As[/COLOR] [COLOR=navy]Integer[/COLOR]
 [COLOR=navy]Dim[/COLOR] Last [COLOR=navy]As[/COLOR] [COLOR=navy]Integer[/COLOR]
 [COLOR=navy]Dim[/COLOR] i [COLOR=navy]As[/COLOR] [COLOR=navy]Integer[/COLOR]
 [COLOR=navy]Dim[/COLOR] j [COLOR=navy]As[/COLOR] [COLOR=navy]Integer[/COLOR]
 [COLOR=navy]Dim[/COLOR] Temp [COLOR=navy]As[/COLOR] [COLOR=navy]String[/COLOR]
 [COLOR=navy]Dim[/COLOR] list [COLOR=navy]As[/COLOR] [COLOR=navy]String[/COLOR]
 First = LBound(MyArray)
 Last = UBound(MyArray)
    [COLOR=navy]For[/COLOR] i = First To Last
        [COLOR=navy]For[/COLOR] j = i + 1 To Last
            [COLOR=navy]If[/COLOR] Val(MyArray(i)) > Val(MyArray(j)) [COLOR=navy]Then[/COLOR]
                Temp = MyArray(j)
                MyArray(j) = MyArray(i)
                MyArray(i) = Temp
            [COLOR=navy]End[/COLOR] If
        [COLOR=navy]Next[/COLOR] j
 [COLOR=navy]Next[/COLOR] i
'[COLOR=green][B] For i = 1 To UBound(MyArray)[/B][/COLOR]
'[COLOR=green][B] list = list & vbCrLf & MyArray(i)[/B][/COLOR]
'[COLOR=green][B] Next[/B][/COLOR]
 MsgBox Join(MyArray, vbLf)
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]

Regards Mick