CODEWITHSUNDEEP
excelvba
FaruSZ
FaruSZ

Reputation: 61

Remove duplicates in VBA Combobox

I have the below code to populate a listbox, therefore I want to remove duplicates from my combobox. I Don't know how to do it:

 Private Sub CommandButton1_Click()
     Dim ws_suivi As Worksheet
    Set ws_suivi = ActiveWorkbook.Worksheets("suivi")
    Fin_Liste_suivi = ws_suivi.Range("A65530").End(xlUp).Row
    
    For i = 2 To Fin_Liste_suivi
        UserForm_SDE.ComboBox_Type_Rapp.AddItem ws_suivi.Range("AD" & i)
    Next
    
    UserForm_SDE.Show
    
    End Sub

Upvotes: 0

Views: 648

Answers (3)

freeflow
freeflow

Reputation: 4355

It is often worth searching to see if a Library for VBA exists that will save you reinventing the wheel.

It is a particular annoyance of VBA that whilst we have such useful structures as Collections and Scripting.Dictionaries there is no easy way to get information into such objects or to do much processing of the data once those objects are populated.

I had a project which had a lot of processing of arrays/scripting.dictionariews and to make my life a little easier I created a VBA library in C# called Kvp (for Key Value Pairs) which is a bit like a Scripting.Dictionary on steriods.

You can download the library, source code, documentation for the Kvp object from here

Once you have added a reference to the Kvp library you can declare a Kvp object in the standard way.

Dim myKvp as Kvp
Set myKvp=New Kvp

You can then add a 1D range from an excel spreadsheet in a single statement

myKvp.AddByIndexFromArray <excel range>.Value

which gives a Kvp of long integers vs cell values

The OP wishes a list of unique values. To do this with a Kvp we can use the Mirror method to create a Kvp of the unique values.

Dim myMirroredKvp as Kvp
set myMirroredKvp=myKvp.Mirror

The Mirror method returns a Two item Kvp where item 0 is a Kvp of unique items vs the first Key at which the item was found and item 1 is a Kvp of original Keys vs value where the values are a duplicate.

You can then get an array of the keys using the GetKeys method

Dim myUniqueValues as Variant 
myUniqueValues = myMirroredKvp.GetItem(0).GetKeys

Or should you want the items sorted in reverse order

myUniqueValues - myMirroredKvp.GetItem(0).GetKeysDescending

The above can be shortened to

myUniqueValues = myKvp.Mirror.GetItem(0).GetKeysDescending

I've found the Kvp library quite useful. I hope you do to!!

Upvotes: 3

Macronaute
Macronaute

Reputation: 196

I found :

Dim Valeur As String
    Dim i As Integer
    Dim j As Integer
    'For each element in the list
    For i = 0 To lst_ref.ListCount - 1
       Valeur = Combobox.List(i)
        For j = i + 1 To Combobox.ListCount - 1
            'If the element exist, delete it
            If Valeur = Combobox.List(j) Then
                Call Combobox.RemoveItem(j)
            End If
        Next j
    Next i

It take the beggining of the combobox and check if the value is red again in to the end of the combobox.

Upvotes: 1

Chronocidal
Chronocidal

Reputation: 8081

While you could load the list to a Dictionary, you might find it simpler to try using WorksheetFunction.CountIf to check if the item is further up your list (and has, thus, already been included):

If (i=2) OR (WorksheetFunction.CountIf(ws_suivi.Range(ws_suivi.Cells(2,30),ws_suivi.Cells(i-1,30)), ws_suivi.cells(i,30).Value)<1) Then
    UserForm_SDE.ComboBox_Type_Rapp.AddItem ws_suivi.Range("AD" & i)
End If

As a side-note: Since Excel 2007 increased the Row Limit from 65536 (216) to 1048576 (220), you may want to change Fin_Liste_suivi = ws_suivi.Range("A65530").End(xlUp).Row to Fin_Liste_suivi = ws_suivi.Cells(ws_suivi.Rows.Count, 1).End(xlUp).Row

Upvotes: 1

Related Questions