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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 |
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!