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

Leave a Reply


Advertisement Peltier Tech Chart Utilities for Excel PTS Waterfall Chart Utility Peltier Tech Box and Whisker Chart Utility Peltier Tech Cluster-Stack Chart Utility Peltier Tech Panel Chart Utility Peltier Tech Marimekko Chart Utility Peltier Tech Dot Plot Utility Peltier Tech Cascade Chart Utility