Hi,

My plan is to have a form open when the workbook is opened, and the
form will contain a combobox with a list of names for the user to
choose from. The worksheet 'QueryBuster' is loaded with data, and
column N has all the assigned names. I want to look at column N and
extract a unique list of names and populate the combobox with that
list.

I've found multiple examples in my Google search, but I can't seem to
make this work. In my code below, I'm trying to take column N (the
names) in the QueryBuster sheet, and copy the unique filtered names to
A1 on the Names sheet. Then from there, load the combo box with that
list of names.

What might I be doing wrong? Thanks for any help!


Private Sub UserForm_Initialize()

Dim rnNames As Range
Dim wsSheet As Worksheet
Dim wsNames As Worksheet
Dim vaNames As Variant ' the Names list stored as a variant
Dim vaItem As Variant 'a variant representing the type of
'items in ncData

Set wsSheet = Worksheets("QueryBuster")

With wsSheet
Set rnNames = .Range(.Range("N2"), .Range("N100000").End(xlUp))
End With

Set wsNames = Worksheets("Names")

With wsNames
rnNames.AdvancedFilter Action:=xlFilterCopy, _
CopytoRange:=.Range("A1"), Unique:=True

' store unique values in vaNames
vaNames = .Range(.Range("A1"), .Range("A500").End(xlUp)).Value

' clean up contents of names on Names sheet so it will be clean
next time
.Range("A:A").ClearContents
End With

'For Each vaItem In vaNames
' .AddItem vaNames(vaItem)
'Next vaItem

End Sub