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
name.
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
combo box:
Sub RemoveDuplicates()
ActiveWorkbook.Sheets("Billing").Activate
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
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
frmfilter.cmbclient.AddItem Item
Next Item
' Show the UserForm
frmfilter.Show
End Sub
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?