Delete duplicates from VBA Combobox

[DeepBlue1]

Hi Everyone,

I've got a list of ticket number in my excel and some of them are duplicates, which is not a problem.
But I don't want to see these duplicates in my Combobox drop down list.

Does anyone have a clue on this ? I've already written a code but it doesn't work.


Private Sub UserForm_Initialize()

Dim D As IntegerFor D = 5 To Range("I10").End(xlUp).Row
Me.Cbx_ticket = Range("I" & D)If Me.Cbx_ticket.ListIndex = -1 Then Me.Cbx_ticket.AddItem Range("I" & D)
Next D

Me.Label_info.Caption = Sheets("Config").Range("e24")
End Sub

[snb]

Use advancedfilter to create a duplicate-free list.

[p45cal]

try:
Private Sub UserForm_Initialize()
Me.Cbx_ticket.List = [unique(ticket)]
Me.Label_info.Caption = Sheets("Config").Range("e24")
End Sub
and remove ticket from RowSource property:

27726
(I hope you have UNIQUE available as a worksheet function)

[kadrl]

Hi,
- Remove "ticket" from Cbx_ticket -Properties -RowSource section
- Add this codes to Userform_Initialize procedure :

Dim a As Variant, hcr As Range, i As Long, j As Long, x As Variant
With CreateObject("Scripting.Dictionary")
For Each hcr In Sheets("DT").Range("I5:I" & Cells(Rows.Count, 9).End(3).Row).SpecialCells(xlCellTypeVisible)
If Not .exists(hcr.Value) Then
.Add hcr.Value, Nothing
End If
Next hcr
a = .keys
End With
For i = LBound(a) To UBound(a) - 1
For j = i + 1 To UBound(a)
If StrComp(a(i), a(j)) = 1 Then
x = a(j)
a(j) = a(i)
a(i) = x
End If
Next j
Next i
On Error Resume Next
Me.Cbx_ticket.List = a


The sorted unique values can be filled to combobox with this codes.

See you