Excluding duplicate values in combobox

Question

Can someone please tell me how to add a code to exclude duplicate values inside the combobox? I already exclude some lines that have certain values in a colomn (for example when the work is already done) If it is not possible to do in in 1 code, it is also ok to remove the duplicates after the combobox list is made. But for this I also cannot figure out how to manage.

Here is the code for my combobox listing. It is placed inside the Userform1_initialize section.

    Dim LastRow As Long
    Dim aCell As Range

    Set ws = Sheets("Requests")
With ws

LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row

    For Each aCell In .Range("B3:B" & LastRow)
'dicard rows in dropdown for ease of use
        If aCell.Value  "" And aCell.Offset(0, 25).Value = "" And aCell.Offset(0, 22).Value  "on hold" And aCell.Offset(0, 22).Value  "cancelled" Then

        Me.ComboBox2.AddItem aCell.Value

    End If

Next
End With

But you want to load in the combo cells having Value = ""... Why that? You firstly must exclude the cells in B:B being empty and only after that to fill the combo with ones respecting your conditions... — FaneDuru
See comment below. There was a bug in copying the formula. It should be <> and not = — Wesley

Tim Williams Tim Williams · Accepted Answer · 2020-03-02T22:07:35

You can use a scripting dictionary (assuming you're not on a mac):

Dim dict, ws As Worksheet
Dim LastRow As Long
Dim aCell As Range

Set ws = Sheets("Requests")
Set dict = CreateObject("scripting.dictionary")

With ws

    LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row

    For Each aCell In .Range("B3:B" & LastRow)

        'had to guess here since your Q is missing operators ...
        If aCell.Value <> "" And aCell.Offset(0, 25).Value = "" And _
           aCell.Offset(0, 22).Value <> "on hold" And _
           aCell.Offset(0, 22).Value <> "cancelled" Then

            If Not dict.exists(aCell.Value) Then '? new value ?
                Me.ComboBox2.AddItem aCell.Value
                dict(aCell.Value) = True 'add this value
            End If

        End If

    Next
End With

Excluding duplicate values in combobox

1 Answers