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

Private Sub CommandButton1_Click()

Dim i As Long
Dim j As Long
Dim sTemp As String
Dim sTemp2 As String
Dim LbList As Variant

'Store the list in an array for sorting
LbList = Me.ListBox1.List

'Bubble sort the array on the first value
For i = LBound(LbList, 1) To UBound(LbList, 1) - 1
For j = i + 1 To UBound(LbList, 1)
If LbList(i, 0) > LbList(j, 0) Then
'Swap the first value
sTemp = LbList(i, 0)
LbList(i, 0) = LbList(j, 0)
LbList(j, 0) = sTemp

'Swap the second value
sTemp2 = LbList(i, 1)
LbList(i, 1) = LbList(j, 1)
LbList(j, 1) = sTemp2
End If
Next j
Next i

'Remove the contents of the listbox
Me.ListBox1.Clear

'Repopulate with the sorted list
Me.ListBox1.List = LbList

End Sub

And the sorted ListBox

SortMulti2

6 Comments

  1. sudeep says:

    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. andy says:

    Bird, Reptile, Fish, Mammal is alphabetic? I could have sworn R would come after F and M!

  3. raimund says:

    hello , can i use those lists also in vb.net ?
    i couldn’t find multicolumn-lists there
    regards
    raimund

  4. HunnyBean says:

    No andy, “Finch, Gilla Monster, Shark, Zebra” is alphabetic…

  5. Surprising says:

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

  6. Gil MacDougall says:

    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

Here's how to update your reports of company and nearly any web data: