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

Share Button
Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , , ,
3 comments on “Tom’s Tutorials For Excel: Populating and Sorting Unique Items in a ListBox or ComboBox
  1. Gideon says:

    Hi Tom

    Thanks for the tutorial. Is it possible to forward me the excel file for the above ?

    Also is the combobox dependant on the first listbox and the radio button or Color list again dependent on
    the second combobox.

    Regards
    Gideon

    • Tom Urtis says:

      Hi Gideon – –

      Thank you for following my blog. For that level of prepared examples, I’m putting together workbooks associated with these blog posts and making them available on my Store page. When they’re completed, I’ll announce on my blog and on Twitter how to download them.

  2. Kadr Leyn says:

    Thanks for knowledge.
    I made an example of this issue (date ascending order and fill combobox with unique items).
    Here : https://netmerkez.wordpress.com/2015/07/26/filtering-between-two-dates-sort-dates-remove-duplicates-in-combobox/

Leave a Reply

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

*