Consulting

Results 1 to 4 of 4

Thread: Delete duplicates from VBA Combobox

  1. #1

    Delete duplicates from VBA Combobox

    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.

    HTML Code:
    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
    Attached Files Attached Files

  2. #2
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,635
    Use advancedfilter to create a duplicate-free list.

  3. #3
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    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:

    2021-01-13_230520.jpg
    (I hope you have UNIQUE available as a worksheet function)
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  4. #4
    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
    Attached Files Attached Files

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •