Sort Excel VBA Number and Text Array with FUNCTION

Question

If I have values in cell A2=2,4,8,6,12,19,18,23,35,78,101,38,30,205,2 And I want to sort by smallest to largest or largest to smallest in cell B2. then my desired result should be =2,2,4,6,8,12,19,18,23,30,35,38,78,101,101,205 or,Large to small= 205,101,101,78,38,35,30,23,18,19,12,8,6,4,2,2 if I have textvaluse like in A3= WPN/01,AFF/02,PROP/4,ENG/03 Then I want to sort alphabetically my desired result should be in cell B3=AFF/02,ENG/03,PROP/4,WPN/1

See if this helps .. https://stackoverflow.com/questions/44077753/excel-vba-sort-array-function ... and this ... https://stackoverflow.com/questions/152319/vba-array-sort-function — Naresh, Mar 22 '20 at 07:34

Naresh · Accepted Answer · 2020-03-22T14:33:59.817

1

Following function will work for numbers, text and alphanumeric (numbers and text both) strings. Default srtCriteria is set to 0. So, if it is 0 or not mentioned the array will be sorted ascending, else if srtCriteria = 1 then descending.

Function SortArr(myString As String, deLmt As String, Optional srtCriteria = 0)
'myString is deLmt seperated string
'srtCriteria is criteria to sort; 0 or nothing for Ascending, Other digit for descending.
Dim Lb As Long, Ub As Long, i As Long, j As Long
Dim arr, reverseArray
Dim strTemp As String

arr = Split(Trim(myString), deLmt)
Lb = LBound(arr)
Ub = UBound(arr)
For i = Lb To Ub - 1
    For j = i + 1 To Ub
        If IsNumeric(arr(i)) = True And IsNumeric(arr(j)) = True Then
            If Val(arr(i)) > Val(arr(j)) Then
            strTemp = arr(i)
            arr(i) = arr(j)
            arr(j) = strTemp
            End If
        Else
            If (arr(i)) > (arr(j)) Then
            strTemp = arr(i)
            arr(i) = arr(j)
            arr(j) = strTemp
            End If
        End If
    Next j
Next i

If srtCriteria = 0 Then
    SortArr = Join(arr, deLmt)
    Else
    ReDim reverseArray(Ub)
        For i = 0 To Ub
            reverseArray(i) = arr(Ub - i)
        Next
    SortArr = Join(reverseArray, deLmt)
End If

End Function

edited Mar 22 '20 at 14:33

answered Mar 22 '20 at 08:20

Naresh

2,984
2
9
15

1

Milan your doubt is valid.. If a string contains text and numbers it will work in absurd manner for numbers. So editing my answer.. MIX function gives better result. – Naresh Mar 22 '20 at 08:59
Ok Great Sir It worked well.Thanks so much – Milan Kumar Mar 22 '20 at 09:09
Sir If I want to sort by large to small , then where edit in the formula – Milan Kumar Mar 22 '20 at 09:31
1

edited my answer to reverse the sort. – Naresh Mar 22 '20 at 10:16
Yaa it worked Nice you're doing nice coding Great. I want to become your Student pls. – Milan Kumar Mar 22 '20 at 11:00
1

I am student myself... There are many gurus on this platform whom we can follow. – Naresh Mar 22 '20 at 11:03
Ok Sir, But you explain very easily all thing in a very decent manner .Follow you I'm also living in Pune.If needs I can call you Sir – Milan Kumar Mar 22 '20 at 11:08
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/210097/discussion-between-milan-kumar-and-naresh-bhople). – Milan Kumar Mar 22 '20 at 11:13

Sort Excel VBA Number and Text Array with FUNCTION

1 Answers1

Linked