What you've given me there works well, I did have to tweak it slightly though as the names were just for example and your code told the combobox to start with the name "Anonymous" showing. I have altered the code to look at F27 so that the word "Name" becomes that first entry.

I'm puzzled though, because I didn't touch some of the code that refers to "anonymous" in the userform initialize sub, and even when i changed the name anonymous in my table to something else, there was no error - what is 
 If MyArray(Count, 1) <> "Anonymous" Then UniqueItem.Add CStr(MyArray(Count, 1)), CStr(MyArray(Count, 1))
doing?



Private Sub Combobox1_Change()

'Find All Matching Entries
   
    ListBox1.Clear ' added this to clear listbox when new name selected
    Set rngLook = Range("F27:F" & Range("A40000").End(xlUp).Row) 'changed to F27
    strValueToPick = ComboBox1.Value
    With rngLook
        Set rngFind = .Find(strValueToPick, .Cells(1, 1), LookIn:=xlValues, lookat:=xlWhole)
        If Not rngFind Is Nothing Then
            strFirstAddress = rngFind.Address
            Set rngPicked = rngFind
            Do
                Set rngPicked = Union(rngPicked, rngFind)
                Set rngFind = .FindNext(rngFind)
            Loop While Not rngFind Is Nothing And rngFind.Address <> strFirstAddress
        End If
    End With
    
    If Not rngPicked Is Nothing Then
        rngPicked.Offset(0, -3).Select
        
        'Fill Listbox
        For Each c In Selection
        ListBox1.AddItem c.Value
        Next
        
    End If
    
End Sub

Private Sub UserForm_Activate()

    Dim UniqueItem As Collection, cell As Range, N As Long
    
'Read your list of names
MyArray = Range("F27:F" & Range("A40000").End(xlUp).Row).Value ' changed to F27
'Remove Duplicates

    
    Set UniqueItem = New Collection
    
    UniqueItem.Add "Name", "Name" ' Changed from Anonymous to Name
    
    On Error Resume Next
    
    For Count = 1 To UBound(MyArray)
    
    If MyArray(Count, 1) <> "Anonymous" Then UniqueItem.Add CStr(MyArray(Count, 1)), CStr(MyArray(Count, 1))
    Next
    On Error GoTo 0

For Count = 1 To UniqueItem.Count

    ComboBox1.AddItem UniqueItem(Count)
    Next

ComboBox1.ListIndex = 0


End Sub