Excel VBA - Loop to fill ComboBox that doesn't add duplicates
I am filling a ComboBox in a userform using a loop, it adds all the "names" in the first column of the table that are of the same "type" in the second column.
I don't want to go into details, but it is possible that the same "name" occurs multiple times. I don't want the loop to add these duplicate values.
I found several solutions on other forums, but they looked very outdated to me and I feel like it should be easy to fix. (These "legacy" solutions are like 30+ suggestion codes, I don't feel like I need it)
Can anyone help me further?
This is the fill cycle:
With resourceSheet.ListObjects("Table3")
For x = 2 To .ListRows.Count + 1
If .Range(x, 2) = cType Then
'cbname is the combobox
cbName.AddItem .Range(x, 1)
End If
Next x
End With
source to share
Try the following:
' Create Dictionary object
Dim obj As Object
Set obj = CreateObject("Scripting.Dictionary")
With resourceSheet.ListObjects("Table3")
For x = 2 To .ListRows.Count + 1
If .Range(x, 2) = cType Then
' If name doesn't exist in the Dictionary object yet, add the name to the listbox and the Dictionary object
If IsEmpty(obj.Item(.Range(x, 1) & "")) Then
'cbname is the combobox
cbName.AddItem .Range(x, 1)
obj.Item(.Range(x, 1) & "") = .Range(x, 1)
End If
End If
Next x
End With
The dictionary object allows a name to be used as a key. If the key doesn't exist, it adds the name to the list and adds the key. The next time it comes across the same name, this key already exists, so it can move to the next line.
source to share
These "legacy" solutions are like 30+ suggestion codes, I don't feel like I need to
Although you already have the answer, here is another option using collections
Sub Sample()
Dim Col As New Collection, itm As Variant
With resourceSheet.ListObjects("Table3")
For x = 2 To .ListRows.Count + 1
If .Range(x, 2) = cType Then
On Error Resume Next
Col.Add .Range(x, 2).Value, CStr(.Range(x, 2).Value)
On Error GoTo 0
End If
Next x
End With
For Each itm In Col
cbName.AddItem itm
Next
End Sub
source to share