Populate a MultiColumn ListBox From an Unsorted Range

In Sorting a Multicolumn ListBox, I give an example of how to sort a ListBox with two columns. If you have two or three columns, this example is as good as any. However, when you have a lot of columns, the bubble sort can get a little unwieldy. This is the perfect time to use Excel’s built in sort feature.

Assume you have a five column range on a worksheet that you need to leave unsorted (or sorted on some other key), like an External Data Range.

SortMulti3

This is part of an External Data Query from Northwind’s Customer table that is unsorted. To put all these columns in a ListBox sorted on the City field, I copy this range to a new worksheet, sort the Range, then use the ListBox’s List property to put the sorted values into the ListBox. Finally, I delete the temporary worksheet.

The ListBox looks like this

SortMulti4

14 thoughts on “Populate a MultiColumn ListBox From an Unsorted Range

  1. Hi there! I enjoy your site, you have a lot of great information! I have a question for you though…

    I’m doing something similar to this, but was wondering if it’s possible to sort the data in the listbox, while in the listbox itself, say, by clicking on the different headings of columns? Any ideas? Thanks!

  2. Betsy: Thanks. That should be possible. It’s hard to determine columns when clicking in a listbox, or so I’ve found. Rows are easy, but not so much for columns. I’ll mess around with it and see if I can come up with something useful.

  3. Betsy,

    When you have to deal with different alignments per column or other column-related activities, you should look at the ListView control. I’m not sure if it’s included in Windows or if it comes with Visual Studio (or VB). But if you have it on your machine, you may want to take a look at it.

    I have a sample workbook if you’d like me to email it to you.

  4. Betsy,

    I would add a row of CommandButtons just above the listbox that would look like headers (headers are a good idea anyway). They could pass the column to sort on as a parameter to Dick’s code. You could even toggle ascending and descending sorts with successive clicks.

    Dave

  5. I actually do this with a row of labels. I give the labels the button type appearance and put the sort into their on-click event. The label buttons make less obtrusive headers than command buttons.

  6. Hello,

    It’s verry fast to sort with your code.
    In the listbox, i’d like to modify an element (a colomn from a row)Or
    the most interesting, is to paste directly data in a raw.

    It’s verry kind if you could help me.

    bye
    P.

  7. Hi!

    Thank you for this website. It is full of good ideas. Publish a book! (or did you?)

    I have a question…

    If the data is just a worksheet, then what changes in this code?

    Also, whats the difference between a worksheet and a sheet!!!!!????

    Best regards,

    Antoine Dubuc

  8. Hi,
    i am software engg working in tokyo,japan.
    in my project i have to sort the list coloumn if its corresponding header is clicked.
    i need its solution as soon as possible,it is very very urgently required.
    please help me with the solution.

    rgds
    pradeep

  9. Hi ,

    I tried the code. But this portion is not working:

    With Sheet1.QueryTables(1).ResultRange
    Set rCopy = .Offset(1).Resize(.Rows.Count – 1)
    End With

    Could anyone help?

  10. Instead of add & delete temporary worksheet, I’ve used the code found from the book “Numerical Recipes in C#”. Just create a ComboBox to select which column in the ListBox to be set as first key sort order.

    Private Sub CommandButton1_Click()
    Application.ScreenUpdating = False

    ReDim SortedList(TotalRow, 4)
    For k = 0 To TotalRow
        For i = 0 To 4
        SortedList(k, i) = ListBox1.List(k, i)
        Next i
    Next k

    SortByColumn = ComboBox1.ListIndex
    Call SimpleSort(TotalRow, SortedList, SortByColumn)

    Application.ScreenUpdating = True
    End Sub

    Sub SimpleSort(TotalRow, SortedList, SortByColumn)
    Dim tmp(5)

    For k = 1 To TotalRow

        For i = 0 To 4
        tmp(i) = SortedList(k, i)
        Next i

    For j = k – 1 To 0 Step -1

            For i = 0 To 4
            If SortByColumn = i And SortedList(j, i) < tmp(i) Then GoTo Line2
            Next i

            For i = 0 To 4
            SortedList(j + 1, i) = SortedList(j, i)
            Next i

        Next j
        j = -1

        Line2:
        For i = 0 To 4
        SortedList(j + 1, i) = tmp(i)
        Next i

    Next k

    End Sub

    There are other quick sorting methods where you can find in the book.


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

Leave a Reply

Your email address will not be published. Required fields are marked *