Microsoft Office Tutorials and References
In Depth Information
Figure 14-9: A Collection object is used to fill a ListBox with the unique items from column B.
The code loops through the cells in the range and attempts to add the cell’s value to the
NoDupes collection. It also uses the cell’s value (converted to a string) for the key argument.
Using the On Error Resume Next statement causes VBA to ignore the error that occurs if
the key isn’t unique. When an error occurs, the item isn’t added to the collection — which is just
what you want. The procedure then transfers the items in the NoDupes collection to the ListBox.
The UserForm also contains a label that displays the number of unique items.
Dim AllCells As Range, Cell As Range
Dim NoDupes As New Collection
On Error Resume Next
For Each Cell In Range(“State”)
NoDupes.Add Cell.Value, CStr(Cell.Value)
On Error GoTo 0
‘ Add the non-duplicated items to a ListBox
For Each Item In NoDupes
‘ Display the count
UserForm1.Label1.Caption = _
“Unique items: “ & NoDupes.Count
‘ Show the UserForm