combolist array.

[Guest]

IS there a way to create a combolist (vb) which adds everyone in column c and
only adds one instance of the name.

say

name1
name2
name3
name1
name1
name2
name4
name5

which turns into
name1
name2
name3
name4
name5

 

[merjet]

You didn't say if the ComboBox was on a UserForm or Worksheet, but the
coding would be similar. The following is for a UserForm.

Private Sub UserForm_Activate()
Dim iEnd As Long
Dim iRow As Long
Dim NoDupes As Collection
Dim ws As Worksheet

On Error Resume Next
Set ws = Worksheets("Sheet1")
iEnd = ws.Cells(65536, "C").End(xlUp).Row
Set NoDupes = New Collection
For iRow = 1 To iEnd
NoDupes.Add ws.Cells(iRow, "C"), ws.Cells(iRow, "C")
If Err.Number = 0 Then
ComboBox1.AddItem ws.Cells(iRow, "C")
Else
Err.Clear
End If
Next iRow
End Sub

Hth,
Merjet

 

[Bob Phillips]

Dim iLastRow As Long
Dim aryData
Dim i As Long
Dim iItem As Long

With Worksheets("Sheet1")
iLastRow = .Cells(.Rows.Count, "C").End(xlUp).Row
ReDim aryData(1 To iLastRow)
For i = 1 To iLastRow
If IsError(Application.Match(.Cells(i, "C").Value, aryData, 0))
Then
iItem = iItem + 1
aryData(iItem) = .Cells(i, "C").Value
End If
Next i
ReDim Preserve aryData(1 To iItem)
Me.ComboBox1.List = aryData
End With


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)