Can I sort an array?

[UnsolvedCoding]

I have a couple of different reasons why I want to do this, but the bottom line question is this - is it possible to sort the information collected in an array?

 

[SkipVought]

Yes!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[UnsolvedCoding]

Here's the next two questions.

1 - Can I sort the information while its still in the array or do I have to put it somewhere, sort it and then put it back in the array?

2 - If it can be sorted while still in the array how do I do it?

 

[PHV]

The simplest way is Bubble sort

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[UnsolvedCoding]

Thanks

 

[UnsolvedCoding]

For those who are interested I found this, tested it and it works.


Sub Sort_Array()

Dim sortingArray As Variant
Dim i As Long
Dim j As Long
Dim temp As Variant

' Create Array
sortingArray = Selection.Value

For i = 1 To (UBound(sortingArray, 1) - 1)

For j = i To UBound(sortingArray, 1)

If Val(sortingArray(j, 1)) < Val(sortingArray(i, 1)) Then
temp = sortingArray(i, 1)
sortingArray(i, 1) = sortingArray(j, 1)
sortingArray(j, 1) = temp
End If

Next j

Next i

Selection.Value = sortingArray

End Sub

 

[strongm]

I'm confused. Are you trying to sort an array or a range?

If the latter, then Excel has built-in methods for sorting ranges. If the former, then you have complicated things slightly by sourcing the array from a range, because that creates a 2 dimensional array rather than a one dimensional one

 

[N1GHTEYES]

Given strongm's post, you might want to just do this using built-in Excel capabilities. However, if you still want to do your own sorting, you might find bubblesort is a bit slow if you are sorting large arrays.

There are faster sort routines out there. Here is one for example.

Public Sub Quick_Sort(ByRef SortArray As Variant, ByVal First As Long, ByVal Last As Long)
'This sub performs a sort-in-place on the array "SortArray", using the quicksort algorithm.
'SortArray is the array to be sorted
'First - when first called, this should be the lowest index of the array
'Last - when first called, this should be the highest index of the array

Dim Low As Long, High As Long
Dim temp As Variant, List_Separator As Variant
Low = First
High = Last
List_Separator = SortArray((First + Last) / 2)
Do
    Do While (SortArray(Low) < List_Separator)
        Low = Low + 1
    Loop
    Do While (SortArray(High) > List_Separator)
        High = High - 1
    Loop
    If (Low <= High) Then
        temp = SortArray(Low)
        SortArray(Low) = SortArray(High)
        SortArray(High) = temp
        Low = Low + 1
        High = High - 1
    End If
Loop While (Low <= High)
If (First < High) Then Quick_Sort SortArray, First, High
If (Low < Last) Then Quick_Sort SortArray, Low, Last
End Sub

Tony

 

[xlbo]

If the data is in an excel sheet already, why nt sort the data 1st then load to an array?

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[strongm]

That;s the point I was trying to make above, Geoff.

 

[xlbo]

Sorry Mike - more haste n less speed as my Ma used to say!

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[SkipVought]

Acting in a reckless manner causes entropy to increase in the universe. ;-)

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}