Hi guys,

I?m managed to escape from my rehab so here comes a total different approach to create a unique list and populate a combobox in a userform:

Option Explicit

Sub Unique_List()
Dim wbBook As Workbook
Dim wsSheet As Worksheet
Dim i As Long, lnMode As Long
Dim vaData As Variant
Set wbBook = ThisWorkbook
Set wsSheet = wbBook.Worksheets("Blad1")
'Early binding but late binding would propably make it
'a little bit faster.
Dim cnt As ADODB.Connection
Dim rst As ADODB.Recordset
Dim stCon As String, stSQL As String
stCon = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=" & wbBook.FullName & ";" & _
        "Extended Properties=""Excel 8.0;HDR=No"";"
'The keyword DISTINCT create a unique list.
stSQL = "SELECT DISTINCT * FROM [Blad1$A1:A10]"
Set cnt = New ADODB.Connection
Set rst = New ADODB.Recordset
cnt.Open stCon
rst.Open stSQL, cnt, adOpenForwardOnly, adLockReadOnly, adCmdText
With Application
    .ScreenUpdating = False
    lnMode = .Calculation
    .Calculation = xlCalculationManual
    Worksheets.Add Before:=wsSheet
    ActiveSheet.Cells(2, 1).CopyFromRecordset rst
    .Calculation = lnMode
    .ScreenUpdating = True
End With
rst.Close
cnt.Close
Set rst = Nothing
Set cnt = Nothing
vaData = ActiveSheet.UsedRange.Value
With UserForm1.ComboBox1
    .Clear
    .List = vaData
    .ListIndex = -1
End With
UserForm1.Show
End Sub
Since we in Excel has a large workspace to work with dumping data into a worksheet and then manipulate it is both a fast and a reliable way. The benefit will be higher the more data we handle.

Take care,
Dennis