How to filter data in combo Box vba-CodePudding

I got stuck into this problem wherein i have to filter data into combo box. The list should only have unique records. here's the code for populating records into combo box:

Private Sub UserForm_Activate()
Dim myrng As Range
Dim cl As Range
Dim sh As Worksheet
Set sh = Worksheets("Product_Master")

        Set myrng = sh.Range("C2:C100000")
        With Me.comBox_Purchase_Product
            .Clear
            For Each cl In myrng.Cells
                If cl.Value <> "" Then
                    .AddItem cl.Value
                End If
            Next cl
        End With
    End sub

Heres the Products I am getting...now i want only unique records and to remove all duplicate.

Thanks in Advance.

CodePudding user response：

Add all the values into a dictionary first. While adding, test for uniqueness with myDictionary.Exists. Then grab the unique list from the dictionary to load into the combobox list.

Private Sub UserForm_Activate()
    Dim sh As Worksheet
    Set sh = Worksheets("Product_Master")
    
    Dim myrng As Range
    Set myrng = sh.Range("C2:C100000")
    
    Dim Dict As Object
    Set Dict = CreateObject("Scripting.Dictionary")
    
    Dim cl As Range
    For Each cl In myrng.Cells
        If cl.Value <> "" And Not Dict.exists(cl.Value) Then
            Dict.Add cl.Value, 0
        End If
    Next cl

    Me.comBox_Purchase_Product.List = Dict.Keys
End Sub

I suggest changing the event from UserForm_Activate to UserForm_Initialize, because that will avoid re-running the script too many times, but it will work in both events.