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
Set ws = .Worksheets.Add(after:=.Sheets(.Sheets.Count))
'Define the range to copy to the new worksheet
'but exclude the header row
Set rCopy = .Offset(1).Resize(.Rows.Count - 1)
'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
Application.DisplayAlerts = True
The ListBox looks like this