Creating Drop Down Box in Combo Box

Hi Sugi

Thanks for your question

The procedure outlined below, writes unique values to an array from a specified column. You can then use the additem method of the combo box to add each item in the array to the combo box

Sub GetUniqueItems



Dim UniqueList() As String

Dim strCurrentItem As String

Dim i As Integer

Dim j As Integer

Dim k As Integer

Dim blnExists As Boolean



ReDim UniqueList(1)



blnExists = False



UniqueList(1) = Range("A1").Value

k = 2



For i = 2 To Range("A1").CurrentRegion.Rows.Count

blnExists = False

strCurrentItem = Range("a1").Cells(i, 1)

For j = 1 To UBound(UniqueList)

If UniqueList(j) = strCurrentItem Then

blnExists = True

Exit For

End If

Next j

If blnExists = False Then

ReDim Preserve UniqueList(k)

UniqueList(j) = strCurrentItem

blnExists = False

k = k + 1

End If

Next i



For j = 1 To UBound(UniqueList)

Debug.Print UniqueList(j)

Next j



End Sub