Filling A Listbox With Unique Items

Category: UserForms | [Item URL]

A companion file is available: Click here to download

When you display a list of items in a ListBox, you generally don't want to show duplicate items. This tip describes a clever way of filling a UserForm with unique items from a list. This technique is adapted from a tip by J.G. Hussey, published in Visual Basic Programmer's Journal.

How it works

This tip uses a Collection object, and relies on the fact that VBA generates an error if you attempt to add an item to a collection when the item already exists in the collection. The trick is to build the collection by adding all items to it, and ignore the errors that may occur. The result is a collection of unduplicated items.

Example

I created an example to demonstrate the technique. The items (105 of them) are stored in Column A of a worksheet. Many of these items are duplicated. The RemoveDuplicates subroutine, listed below, builds a collection that consists of the unique items in the list. It then transfers the items to a ListBox on a UserForm.

Listing

Following is the VBA listing.

Option Explicit
'   This example is based on a tip by J.G. Hussey,
'   published in "Visual Basic Programmer's Journal"

Sub RemoveDuplicates()
    Dim AllCells As Range, Cell As Range
    Dim NoDupes As New Collection
    Dim i As Integer, j As Integer
    Dim Swap1, Swap2, Item
    
'   The items are in A1:A105
    Set AllCells = Range("A1:A105")
    

'   The next statement ignores the error caused
'   by attempting to add a duplicate key to the collection.
'   The duplicate is not added - which is just what we want!
    On Error Resume Next
    For Each Cell In AllCells
        NoDupes.Add Cell.Value, CStr(Cell.Value)
'       Note: the 2nd argument (key) for the Add method must be a string
    Next Cell

'   Resume normal error handling
    On Error GoTo 0

'   Sort the collection (optional)
    For i = 1 To NoDupes.Count - 1
        For j = i + 1 To NoDupes.Count
            If NoDupes(i) > NoDupes(j) Then
                Swap1 = NoDupes(i)
                Swap2 = NoDupes(j)
                NoDupes.Add Swap1, before:=j
                NoDupes.Add Swap2, before:=i
                NoDupes.Remove i + 1
                NoDupes.Remove j + 1
            End If
        Next j
    Next i

'   Add the sorted, non-duplicated items to a ListBox
    For Each Item In NoDupes
        UserForm1.ListBox1.AddItem Item
    Next Item

'   Show the UserForm
    UserForm1.Show
End Sub