remove duplicates from combo box

[huntmc]

I have a worksheet with range ("B2:B" &FinalRow&) filled with various text values. I want to load these text values into a combo box on a user form, however several of the text values in the worksheet are duplicates of each other. I only want to show unique values in the combo box. How do i get rid of the duplicate items in my combo box?

 

[erik.van.geit]

Hi,

try something like this

Sub test11()
Dim LR As Long
Dim ctrl As Object
Set ctrl = ActiveSheet.ComboBox1
    
LR = Cells(Rows.Count, "B").End(xlUp).Row

ctrl.List() = CreateArray(Range("B6:B" & LR))

End Sub

'creates an array from a given range
'ignores blanks and duplicates

Function CreateArray(r As Range)
    Dim col As New Collection, c As Range, TempArray(), i As Long
    
    'for each cell in range r
    For Each c In r
        On Error Resume Next
        col.Add c.Value, CStr(c.Value)
        If Err.Number = 0 And Trim(c) <> "" Then
            ReDim Preserve TempArray(i)
            TempArray(i) = c.Value
            i = i + 1
        End If
        Err.Clear
    Next

    CreateArray = TempArray
    Erase TempArray

End Function

kind regards,
Erik

 

[huntmc]

thanks... i added your code and it worked

 

[erik.van.geit]

you're welcome !!

don't consider it as "my" code
it's "common" code

 

[Aviles]

Hello, I have a very similar scenario to this. I have tried using the code but it does not work for me. I have a few couple of questions if any one could help me out please...

Does the code above go in the 'Form' code (e.g. UserForm_Initialize), or do you put the code in the specific sheet where the values are held?

My values are contained in column A, in a sheet called "Time", using combobox called "cmbTime". I have only changed the following on the code:

Set ctrl = ActiveSheet.ComboBox1 to Set ctrl = ActiveSheet.cmbTime

LR = Cells(Rows.Count, "B").End(xlUp).Row to LR = Cells(Rows.Count, "A").End(xlUp).Row

ctrl.List() = CreateArray(Range("B6:B" & LR)) to ctrl.List() = CreateArray(Range("A6:A" & LR))

All other code stays the same? Is that correct?

Thanks.

 

[Aviles]

Can any one help please?