excel - Only unique records in a Combobox (VBA)

Question

I have a combobox where I add some stuff from an Excel sheet with a bunch of stuff. I want only unique records and I want them to be updated when I switch to this page. For that I used the following code:

Private Sub MultiPage1_Change()
Dim Rand As Long
Dim ws As Worksheet
Set ws = Worksheets("BD_IR")
Dim i As Long
Rand = 3
Do While ws.Cells(Rand, 3).Value <> "" And Rand < 65536
    If Me.repereche.ListCount <> 0 Then
        For i = 0 To (Me.repereche.ListCount)
        If Me.repereche.List(i, 0) <> Mid(ws.Cells(Rand, 3).Value, 4, 10) Then
            Me.Controls("repereche").AddItem Mid(ws.Cells(Rand, 3).Value, 4, 10)
        End If
        Next i
    ElseIf Me.repereche.ListCount = 0 Then
        Me.Controls("repereche").AddItem Mid(ws.Cells(Rand, 3).Value, 4, 10)
    End If
    Rand = Rand + 1
Loop

The problem with this code (and I don't know where the problem is?) is that whenever I change the page and I come back to the page where this combobox is... it adds more (not unique) and more items. Where am I wrong?

Answer 1

Try this code:

Dim ws As Worksheet
Dim rCell As Range

Set ws = Worksheets("BD_IR")

'//Clear combobox
repereche.Clear

With CreateObject("Scripting.Dictionary")
    For Each rCell In ws.Range("C3", ws.Cells(Rows.Count, "C").End(xlUp))
        If Not .exists(rCell.Value) Then
            .Add rCell.Value, Nothing
        End If
    Next rCell

    repereche.List = .keys
End With

I prefer this over a collection as you can check if the value exists in the dictionary rather than using on error and add the entire collection to the combobox at once.