Populate a ComboBox on a form

[hugs68]

I need to populate a ComboBox in a VBA form with values from a column in a worksheet but I want to remove any duplicate values. The column contains a list of last names and I only want unique values to appear in the ComboBox.

The first names ComboBox on te form is then populated according to the last name selected. This part I have working but I'm stumped as to removing the duplicates!

 

[jindon]

hi assuming name list start from A1 on Sheet1

if you set RowSource property or any other source regarding
data source on property box, clear all, otherwise error

Private Sub UserForm_Initialize()
Dim dic As Object, x, r As Range
Set dic = CreateObject("scripting.dictionary")
dic.comparemode = vbTextCompare
With Sheets("sheet1") ' <-- alter to suite
    For Each r In .Range("a1", .Range("a" & Rows.Count).End(xlUp))
        If Not IsEmpty(r) Then
            If Not dic.exists(r.Value) Then
                dic.Add r.Value, Nothing
            End If
        End If
    Next
End With
x = dic.keys: Set dic = Nothing
Me.ComboBox1.List = x
Erase x
End Sub