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 column (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)
        'discard 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

Answer 1

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