Sort a range in VBA with System.Collections.ArrayList Numerically

Imagine you have a task to sort the range from column A based on the number after the last digit.

A quick decision is to use Column “B” as a helping column where you write the cell, splitted by the “-“. Then you sort the two columns by the helping column and it works. Something like this:

Option Explicit

Public Sub SortMe()

    Dim sortRange   As Range
    Dim sortNumbers As Variant
    Dim myCell As Range

    Set sortRange = Range("A1:A7")

    For Each myCell In sortRange
        myCell.Offset(0, 1) = Split(myCell, "-")(UBound(Split(myCell, "-")))
    Next myCell

    Union(sortRange, sortRange.Offset(0, 1)).Sort Key1:=sortRange.Offset(0, 1)
    sortRange.Offset(0, 1).Clear

End Sub

It will work. No doubt. However, there are at least 2 other ways to make it work. One of the ways is to use “System.Collections.ArrayList” and its built-in method Sort(). The built-in Sort() method, however sorts only alphabetically. With other words “2” is after “1”, “11”, or “1111”. And this is not what we need. Thus, a bit of a work around is to make a second “System.Collections.ArrayList” and in it to put only the numbers after the last dash. Then sort the “Systems.CollectionsArrayList” and find a way to map the sorted list to the initial input. I found a way with 2 nested loops, not really optimal, but in any case faster than adding a second column in Excel.

This is how the code looks like:

Option Explicit

Public Sub SortMe()

    Dim sortRange   As Range
    Dim myCell As Range
    Dim myList As Object
    Dim myListWhole As Object
    
    Set myList = CreateObject("System.Collections.ArrayList")
    Set myListWhole = CreateObject("System.Collections.ArrayList")
    Set sortRange = Range("A1:A7")
    
    For Each myCell In sortRange
        myList.Add CLng((Split(myCell, "-")(UBound(Split(myCell, "-")))))
        myListWhole.Add myCell.Value2
    Next myCell
    
    myList.Sort
    
    Dim cnt As Long
    For Each myCell In sortRange
        Dim strVal As Variant
        For Each strVal In myListWhole
            If Split(strVal, "-")(UBound(Split(strVal, "-"))) = CStr(myList(0)) Then
                myCell = strVal
                myListWhole.Remove (strVal)
                Exit For
            End If
        Next strVal
        myList.Removeat 0
    Next myCell

End Sub

Enjoy it!