Put this in ur sheet-module where u have the list
Dropdown is inserted on Sheet1 C1 - change to fit

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("A1:A9000")) Is Nothing Then Exit Sub
Dim t, List
For t = 1 To Cells(9000, 1).End(xlUp).Row
If Cells(t, 1) < "" Then List = List & "," & Cells(t, 1)
Next
With Sheets("Sheet1").Range("C2").Validation ' where the dropdown will bee
present
.Delete
.Add xlValidateList, Formula1:=List
.InCellDropdown = True
End With
End Sub



"Derek Hart" skrev:

I have seen how you can build a combo box in a cell by going to
Data...Validation...Allow:List and using a named range to define a list.
But this list cannot change with some management by removing the named range
and inserting or removing data from the range, and then naming the range
again. I wish to make it easy for a user to paste a column of data into the
same range, and then have that take automatically in the combo box list. Any
ideas on clever ways to do this? Can I build the list dynamically using vba
..AddItem, or some other way?