Tom’s Tutorials For Excel: Populating and Sorting Unique Items in a ListBox or ComboBox

Tom’s Tutorials For Excel: Populating and Sorting Unique Items in a ListBox or ComboBox

There are times when you need to populate a ListBox or ComboBox with unique items from a dynamic range containing many repeated items. You’ll also want the items to be sorted automatically, to make the interface more user-friendly.

Maybe you can sort a worksheet’s source data and use Advance Filter to get unique items, but many projects require original data to remain untouched. Besides, there’s a better way.

In the picture, a UserForm shows a ListBox and ComboBox that, when called, automatically list and sort only unique items from their respective fields. The below Initialize event code in the UserForm’s module also skips over cells (if there were any) that contain no values.

Private Sub UserForm_Initialize()

'Declare Variables.
Dim Coll As Collection, cell As Range, LastRow As Long
Dim blnUnsorted As Boolean, i As Integer, temp As Variant
Dim SourceSheet As Worksheet
Set SourceSheet = Worksheets("Sheet1")

'///////////////////////////////////////////////////////

'Populate the ListBox with unique Make items from column A.
LastRow = SourceSheet.Cells(Rows.Count, 1).End(xlUp).Row
On Error Resume Next
Set Coll = New Collection
'Open a With structure for the ListBox control.
With ListBoxMake
.Clear
For Each cell In SourceSheet.Range("A4:A" & LastRow)
'Only attempt to populate cells containing a text or value.
If Len(cell.Value) <> 0 Then
Err.Clear
Coll.Add cell.Text, cell.Text
If Err.Number = 0 Then .AddItem cell.Text
End If
Next cell

'Sort the ListBox items in ascending order.
blnUnsorted = True
Do
blnUnsorted = False
For i = 0 To UBound(.List) - 1
If .List(i) > .List(i + 1) Then
temp = .List(i)
.List(i) = .List(i + 1)
.List(i + 1) = temp
blnUnsorted = True
Exit For
End If
Next i
Loop While blnUnsorted = True
'Close the With structure for the ListBox control.
End With

'///////////////////////////////////////////////////////

'Populate the ComboBox with unique Style items from column B.
LastRow = SourceSheet.Cells(Rows.Count, 2).End(xlUp).Row
On Error Resume Next
Set Coll = New Collection
'Open a With structure for the ComboBox control.
With ComboBoxStyle
.Clear
For Each cell In SourceSheet.Range("B4:B" & LastRow)
'Only attempt to populate cells containing a text or value.
If Len(cell.Value) <> 0 Then
Err.Clear
Coll.Add cell.Text, cell.Text
If Err.Number = 0 Then .AddItem cell.Text
End If
Next cell

'Sort the ComboBox items in ascending order.
blnUnsorted = True
Do
blnUnsorted = False
For i = 0 To UBound(.List) - 1
If .List(i) > .List(i + 1) Then
temp = .List(i)
.List(i) = .List(i + 1)
.List(i + 1) = temp
blnUnsorted = True
Exit For
End If
Next i
Loop While blnUnsorted = True
'Optional to show first item as default.
.ListIndex = 0
'Close the With structure for the ComboBox control.
End With

'///////////////////////////////////////////////////////

'Release Object variables from system memory.
Set Coll = Nothing
Set SourceSheet = Nothing

End Sub