Combo box display

[av8tordude]

I would like to display items from column C9:C10009 in a combo box. There are some items that are repeated, so if possible to only duplicate items once in the drop down box. Can someone assist. Thx

[Kenneth Hobs]

http://www.vbaexpress.com/forum/showthread.php?t=43616

[av8tordude]

Thank you Ken, but I have names in column C that are duplicated. Is it possible to only show duplicate names only once in the drop down display? For example...

Test1
Test2
Test3
Test2
Test4
Test1

Show only Test1, Test2, Test3, Test4

[av8tordude]

I did a search and I found what I was looking for, but with this code, can someone assist in putting the display in alpha-numerical order in the drop down box

Private Sub UserForm_Initialize()
Dim sRange As Range
Set sRange = Range("C9", Range("C10009").End(xlUp))
FillCtrlUnique sRange, Me.ComboBox1
End Sub

Sub FillCtrlUnique(myRange As Range, myControl As Control)
Dim Coll As New Collection
Dim var As Variant
Dim cell As Range

On Error Resume Next
For Each cell In myRange
Coll.Add Item:=cell.Value, Key:=CStr(cell.Value)
Next cell
On Error GoTo 0

With myControl
.Clear
For Each var In Coll
.AddItem var
Next var
End With
Set Coll = Nothing
End Sub

[av8tordude]

I found a sorting code...but can someone assist in putting the two codes together...Appreciate the assistance..thx

Dim sortingArray As Variant, add As Integer
Dim i As Long, j As Long, temp As Variant
sortingArray = Range(Range("C9"), Range("C" & Rows.Count).End(xlUp))
For i = 1 To (UBound(sortingArray, 1) - 1)
For j = i To UBound(sortingArray, 1)
If sortingArray(j, 1) < sortingArray(i, 1) Then
temp = sortingArray(i, 1)
sortingArray(i, 1) = sortingArray(j, 1)
sortingArray(j, 1) = temp

End If
Next j
Next i
With ComboBox1
For add = 1 To UBound(sortingArray)
.AddItem sortingArray(add, 1)
Next
End With

[Kenneth Hobs]

There are many ways to get unique values and then sort them. Here is one way. I could have also used a worksheet method to remove duplicates. Lots of ways to do these things.

In a Userform:
Private Sub UserForm_Initialize()
Dim r As Range, vRange As Variant, s As String
Set r = Range("C9", Cells(Rows.Count, "C").End(xlUp))
vRange = UniqueValues(r)
ComboBox1.List = SortArray(vRange)
End Sub

In a Module:
'http://msdn.microsoft.com/en-us/library/aa730921.aspx
'http://www.mrexcel.com/forum/showthread.php?t=329212
Public Function UniqueValues(theRange As Range) As Variant
Dim colUniques As New VBA.Collection
Dim vArr As Variant
Dim vCell As Variant
Dim vLcell As Variant
Dim oRng As Excel.Range
Dim i As Long
Dim vUnique As Variant
Set oRng = Intersect(theRange, theRange.Parent.UsedRange)
vArr = oRng
On Error Resume Next
For Each vCell In vArr
If vCell <> vLcell Then
If Len(CStr(vCell)) > 0 Then
colUniques.Add vCell, CStr(vCell)
End If
End If
vLcell = vCell
Next vCell
On Error GoTo 0

ReDim vUnique(1 To colUniques.Count)
For i = LBound(vUnique) To UBound(vUnique)
vUnique(i) = colUniques(i)
Next i

UniqueValues = vUnique
End Function

Public Function SortArray(ByRef MyArray As Variant, Optional Order As Long = xlAscending) As Variant
Dim w As Worksheet
Dim r As Range

Set w = ThisWorkbook.Worksheets.Add()

On Error Resume Next
Range("A1").Resize(UBound(MyArray, 1), 1) = WorksheetFunction.Transpose(MyArray)
Range("A1").Resize(UBound(MyArray, 1), UBound(MyArray, 2)) = WorksheetFunction.Transpose(MyArray)
Set r = w.UsedRange
If Order = xlAscending Then
r.Sort Key1:=r.Cells(1, 1), Order1:=xlAscending
Else
r.Sort Key1:=r.Cells(1, 1), Order1:=xlDescending
End If

SortArray = r

Set r = Nothing
Application.DisplayAlerts = False
w.Delete
Application.DisplayAlerts = True
Set w = Nothing
End Function