CODEWITHSUNDEEP
excelvba
javad
javad

Reputation: 105

Excel VBA adding items to combo box without repeated items

I want to add below items to combobox but if there are duplicates of an item then only one should be added.

   A
1 john  
2 john
3 marry
4 marry
5 john
6 lisa
7 frank
8 marry

I want the combobox result to be john, marry, lisa and frank (four unique items instead of eight items).

My code is:

Private Sub Workbook_Open()

    Application.EnableEvents = False

    With Sheet2.ComboBox1

        For Each Cell In Sheet1.Range("A1:A6348")
            If Not ComboBox1.exists(Cell.Value) Then
                .AddItem  Cell.Value
            End If
        Next

    End With

End Sub

Upvotes: 3

Views: 15461

Answers (2)

basodre
basodre

Reputation: 5770

An alternative approach to adding unique items is to use a Dictionary object.

See below:

Dim rngItems As Range
Dim oDictionary As Object

Set rngItems = Range("A1:A8")
Set oDictionary = CreateObject("Scripting.Dictionary")

With Sheet1.ComboBox21
    For Each cel In rngItems
        If oDictionary.exists(cel.Value) Then
            'Do Nothing
        Else
            oDictionary.Add cel.Value, 0
            .AddItem cel.Value
        End If
    Next cel
End With

Upvotes: 5

Davesexcel
Davesexcel

Reputation: 6982

Get Unique Items

Sub UsingCount()
    Dim ws As Worksheet
    Dim Rws As Long, Rng As Range, c As Range, y As Integer, x

    Set ws = Sheets("Sheet1")
    Sheets("Sheet3").ComboBox1.Clear

    With ws

        Rws = .Cells(Rows.Count, "A").End(xlUp).Row

        For y = 1 To Rws

            Set c = .Cells(y, 1)
            Set Rng = .Range(.Cells(2, 1), .Cells(y, 1))

            x = Application.WorksheetFunction.CountIf(Rng, c)

            If x = 1 Then Sheets("Sheet3").ComboBox1.AddItem c
        Next y

    End With

End Sub

Upvotes: 4

Related Questions