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:

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:

Enjoy it!

About

VBA Developer

Tagged with: , ,