New Excel Forum

This forum has been moved to TeachExcel.com

Ask all future questions in the New Excel Forum.

ExcelKey

Sort A Numeric Array In Ascending Order

Free Excel Macros

Sort A Numeric Array In Ascending Order

Postby Sisyphus » Mon Apr 16, 2012 9:28 pm

Sorting numbers is different from sorting text because in a text sort 10 will precede 2 on account of the character 1 having a lower ASCII ranking than the character 2, just like “Ab” would precede “B” in the dictionary. The following procedure sorts numbers.
Code: Select all
Option Base 0

Private Sub SortNumericArray(ByRef A As Variant)
    ' sort numbers in ascending order
   
    Dim Tmp As String
    Dim Done As Boolean
    Dim i As Long
   
    Do
        Done = True
        For i = 1 To UBound(A)
            If Val(A(i)) < Val(A(i - 1)) Then
                Tmp = CStr(A(i))
                A(i) = A(i - 1)
                A(i - 1) = Tmp
                Done = False
            End If
        Next i
    Loop Until Done
End Sub
The sort order is ascending. However, if you wish to sort descending just change the < sign in this line of code to a < sign.
Code: Select all
If Val(A(i)) < Val(A(i - 1)) Then


To call the procedure you need to provide a variant of array type such as you could generate with this code:
Code: Select all
    Dim MyArray As Variant
    MyArray = Array(5,55,3,13,99,17,5,3.314)

If you need to read your numbers from a worksheet code like this will do the job:
Code: Select all
Dim MyArray() As Variant
    Dim R As Long
    Dim i As Long

    For R = FirstRow To LastRow
        ReDim MyArray(i)
        MyArray(i) = Cells(R, “A”).Value
        i = i + 1
    Next R

It is important that your array starts with a lower bound of 0. Therefore you should not omit the Option Base 0 statement at the top of your module.
Use this kind of code to call the procedure:
Code: Select all
     SortNumericArray MyArray

The array is passed to the sub ByRef, meaning that only the address is provided, rather than the array itself. That saves time by not transferring potentially large amount of data. It also means that the original sequence of the MyArray elements is irretrievably lost during the sort. The only way to get it back would be to read it again from the source. If, for some reason, you need the original, unsorted version, send a copy thereof for sorting. In the above procedure call MyArray is itself different before and after.

The procedure here offered was designed for sorting indices. It will do lists from a few dozen to a few hundred items efficiently. Yet, it can’t match Excel’s own sorting capabilities. If you have thousands of items to sort you should consider faster alternatives.
Have a great day! :D

Sisyphus
I do this for "honour and country" - much less of the latter, actually.
If I helped you, award points, plenty of them.
If I bored you, deduct points for being too long-winded. (I know, :lol)
Sisyphus
Former Moderator
 
Posts: 4454
Joined: Dec 7, 2011
Location: Shanghai
Reputation: 203
Excel Version: 2010

Return to Excel Macros

Who is online

Users browsing this forum: No registered users and 25 guests

cron