Sorting a MultiColumn ListBox

This example sorts the items in a two-column ListBox. Here’s what the unsorted example ListBox looks like

SortMulti

The code behind the Sort button looks like this

And the sorted ListBox

SortMulti2

20 thoughts on “Sorting a MultiColumn ListBox

  1. Hi,
    I was wondering if you could help with a simple problem. I am not very used to VBA functions. I was wondering if you could help with a simple VBA routine that allows me to sort the data in a column in descending order. I have 3 columns with data and want to sort the column 1 in descending order with corresponding data in column 2 and 3 matching the corresponding column 1 data.

    I am able to record macros and do it, but I would need a function that actually creates a user defined function in the dropdown function list in the spreadhsheet. Putting the recorded macros in a function did not help. I guess its very simple for expert user. I would need a user defined function for my problem to be solved.

    Would appreciate your help.
    Thanks a lot

    Sudeep

  2. How can one still use the bubble sort? The worst sorting algorithm invented by man!

  3. The much maligned bubble sort is unsophisticated, rough and crude.
    It is also often times much easier to implement in certain situations.

    I saw comparisons of algorithms for data sets where the quick sort was
    1000 times faster. To the modern microprocessor the difference often is
    1/10000 of a second verses 1/10th of a second ! (oh how impatient we have become :)

    I appreciate a good kludge but i am unappreciative of “sophisticated”
    algorithms that simply do not work. Remember the mantra of programming
    that it is 10 times more difficult to debug than it is to write a program.
    So not only is simpler often the better route, a program can become so complex that
    it transcends the ability of the writer to debug. So lets not just yet throw the bubble
    sort under the bus :)

    Gil

  4. @Ahmed: Change the comparison line to look at the second element of the array instead of the first

    Everything else stays the same.

  5. Hello,

    Thanks for your bubble sort code. It has been a great help as I am a newbie to VBA code.

    I have used your code to populate a list box with 5 columns. The first column (col 0) in the listbox is numerical with numbers between 1 to 40. Unfortunately, the sort on numbers produces something like 1,10,11, 12, 2, 21, 22 etc. Is there anyway I could change your code so that the code could sort the numbers correctly i.e 1,2,3,4 etc.

    I do not know very much about VBA code, so any help you could give would be greatly appreciated.

    Thank you

  6. how do populate 2 columns that are independent values. ie you can select fish in the second column only. When I populate it puts scroll bar on the list box and does not fill the second column

  7. ‘Same code for 3 columns including reverse sorting :
    ‘My columns are File Name, File Type and File Path, so the names of the buttons

  8. Marmouille’s code took it a step further and I love how it accommodates multiple columns. My need is for 8+ columns, which would have made this code very long, so I went even further with an additional loop for the column count. I set up a combobox that populates the column names and based on the listindex, it will sort by that column.

    HOWEVER, I’ve found that the sorting power is limited to the first character, only, both for numbers AND strings.

    so, alphabetically, I see it sort string fields in a way that I see:
    AAA, Copy, Duplicate, Orbital, TRC, This. The last two, should have been reversed. “This” should come before “TRC”, correct?

    In another field, I have Dollar values. It starts with $0.00. Great. I then see $1,224.65, then I see $100.42, $112.27, $190, $193, $528, etc. I think the “,” caused the $1,224 to show up BEFORE the $100. Not good. How do I get around that?

    For dates: 12/14 comes BEFORE 12/8. Maybe that wouldn’t be the case if it was 12/”08″, but it’s not, so what to do?

    Here’s the code for anyone else using listboxes with many columns:

  9. Christopher: There are two types of comparisons that VBA can do: Binary and Text. Binary is the default. That means that

    “TRC” < “This”

    because a capital R has a lower ascii value than a lower case h. If you want to change the default you put Option Compare Text at the top of your module and almost all comparisons will be Text. I never do this. If I need a text compare, I use StrComp.

    A return value of 1 means the first string is greater than the second. See https://msdn.microsoft.com/en-us/vba/language-reference-vba/articles/strcomp-function for all the return values.

    As for numbers, everything in a ListBox is a string – even if it looks like a number. To compare them as numbers, you have to convert them. You could do something like this

  10. Dick, that is Brilliant! Thank you!

    last thing… I have two columns that are date fields: (Created / Modified), which are populated via VBA with c.value = Now

    I notice with your code, everything else works great for both Text AND number fields, but the dates are still wonky. They are formatted like so:

    12/8/17 4:32 PM
    in the format dialog box, these columns are Category: Date, Type: 3/14/12 1:30 PM

    The sort function for these fields has 1/15 showing up sooner than 1/3. 1/6 comes before 12/1. Any ideas on how I can sort them via timestamp?

  11. Never Mind, I got it working by nesting another if statement to handle the listbox columns differently, based on whether the combobox indicated to sort via “Created” or “Modified” (Both of which are date/time fields). If so, then I used the CDate() method to test the strings as dates, which worked wonderfully. Here’s the final code:

  12. Andrew, see Christopher Smith’s code two comments up. He uses CDATE() to convert the Listbox entry to a date while comparing them.


Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.