I have a form with a combo box that pulls 'Client' name data from a
worksheet named 'Billing'.
I want the combo box to show only one occurance of each Client name
from the Billing database and not show/remove any duplicates (without
removing the original duplicated entries in the billing worksheet.
The form is used to filter the Billing database by choosing a client
The current code I have is below which is added to a macro button
which opens the form to filter the Billing database and populates the
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 C2:C999
Set AllCells = Range("C2:C999")
' 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
' 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
' Add the sorted, non-duplicated items to a ListBox
For Each Item In NoDupes
' Show the UserForm
It worked the first time I applied it, only showing one instance of
each name from the Billing table in the combo box, however now I have
since added more billing to the table and it shows duplicates of those
names in the combo box on the filter form.
I dont understand why this is as the remove duplicates macro above
which populates the combo box with distinct values runs each time the
filter form is opened??
Can any one help?