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.
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.
Private Sub UserForm_Initialize()
Dim ws As Worksheet
Dim rCopy As Range
Me.ListBox1.ColumnCount = 5
‘Add a temporary worksheet to sort a range
With ThisWorkbook
Set ws = .Worksheets.Add(after:=.Sheets(.Sheets.Count))
End With
‘Define the range to copy to the new worksheet
‘but exclude the header row
With Sheet1.QueryTables(1).ResultRange
Set rCopy = .Offset(1).Resize(.Rows.Count – 1)
End With
rCopy.Copy Destination:=ws.Range(“A1?)
‘Sort the copied range
ws.Range(“A1?).CurrentRegion.Sort ws.Range(“C1?), xlAscending
‘Populate the listbox
Me.ListBox1.List = ws.Range(“A1?).CurrentRegion.Value
‘Delete the temporary worksheet
Application.DisplayAlerts = False
ws.Delete
Application.DisplayAlerts = True
End Sub
The ListBox looks like this
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!
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.
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.
I am use less
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
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.
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.
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
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
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?
Thank you so much. This article has been a great help!
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.
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.