VBA Merge sort

  • I don't know if this will help anybody, nor can I be sure this code is bullet proof, but what I can say is that I use it an awful lot to quickly sort arrays in VBA and I haven't broken it yet. Could be useful?
    Nb. this mutates the original array so make a copy of your array first if you want one with the original order in place.

  • Neat code, thanks

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Hmmmm... wish I had seen this yesterday... LOL. I’ll try it out later.

    There are 10 types of people in the world. Those that understand Binary and those that dont. :P

    Why are Halloween and Christmas the same? Because Oct 31 = Dec 25... ;)


  • Awesome sauce... very very quick for me on a 2D x 1 column sort with numbers. over 40k rows.. suggestion for improvement... add another optional parameter for ascending or descending... not sure how badly that would screw up the code. Or maybe include instructions to convert between one and the other. I guess its a case of changing all < with > ? :) Thanks mate.


    There are 10 types of people in the world. Those that understand Binary and those that dont. :P

    Why are Halloween and Christmas the same? Because Oct 31 = Dec 25... ;)


  • That's actually a very good idea! I have a reverse array function which I've always used when I wanted to sort in reverse, but it makes so much more sense to have an ascending/descending flag. I'll get on it!

  • There is definitely a more efficient way of doing this, but here's a quick and dirty version with a descending flag as the last optional argument to sort an array in descending order. Just set it to true to take effect.

  • Made some massive changes on this to try and make it slightly more intelligent.

    There is now an optional flag for ascending or descending sorts (unfortunately this is still global as I haven't had time to elegantly do it on a per sort key basis) and I've also fixed a bug if an array contained objects rather than just simple variable types. I've also changed the way the algorithm treats numbers within a text string so "10 Blah" would now sort after "2 Blah" (see example Sub below for what I mean).

    Anyway, here's the updated sort code: sortArray.txt

    And heres a little demo of what I mean about how Strings containing numbers are handled (you can use the autofilter to see what the default sort would have been vs. what the sort code does):

  • This is simply amazing, it helps me a lot. Thank you very much for sharing!!!

    Can I ask if it's possible to move the blank items in the 2D-array ("") to the end instead of the very beginning?

    Either way, thank you for this great job!!!

  • Hey trunten,

    I've humbled over your very useful solution (Thank you!) and I may suggest to you, that you can indicate the sortorder by using negative/positive values within the sortkeys array. For example, if there is an twodimensional array with 4 columns, the user may give the parameter like array(3,-2,1), which means that the columns 3 and 1 will be ascending sorted and column 2 will be descending. I've done this before in a completely different case, but I thought it might be a nice way to solve this.

    Unfortunately I couldn't figure out, where in your code you iterate through the columns, so I can't make a code example (I'm non professional, just learning by doing).

    Excuse my English, best regards from good old germany,


  • 20-mar-22

    Thanks, this is going to very helpful in a few applications that I can think of.

    I have a large array of strings (about 5m) that look like this, where it does not seem to work:

    C1-1120-1840014659568-C21635-1, F11-719-3520015286261-208049-70, C12-1002-2260013005920-C20842-1,... etc

    I pass these in a variant array but it comes back not properly sorted. I need to sort the 2-d array version of this so that I can employ binary searches.


  • Welcome to the Forum

    Please start your own post. Posting in another member's Thread is known as hijacking and is not allowed here. By all means add a link to a Thread that may be related to your question.

  • royUK

    Closed the thread.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!