0 votes
in VBA by Beginner (24 points)
Congratulations

Column A has a lot of names that are repeated more than once and there is a combobox that I would like to fill with just the names with no repetition. How can I do that?

1 Answer

+2 votes
by Skilled (401 points)
selected by
 
Best answer

Hello

You can use Dictionary for that purpose. Try this code

Private Sub UserForm_Initialize()
    Dim i As Long, a, b

    a = Range("A1:A" & Cells(Rows.Count, 1).End(xlUp).Row).Value

    With CreateObject("Scripting.Dictionary")
        For i = 1 To UBound(a)
            b = .Item(a(i, 1))
        Next i
        ComboBox1.List = .Keys
    End With
End Sub

 

by Beginner (24 points)
+1
Great and very swift solution. Thank you very much.
by Skilled (401 points)
You're welcome. Glad I can offer little help.

In fact, I have some ready VBA codes and I have it in my collection.
by Skilled (265 points)
+1

@YasserKhalil, I was very impressed with this code example. As an old-school coder, I always try to minimze variable assignments when the variable is not going to be used elsewhere.

So, I was playing around with your example and used this construct, instead:

Option Explicit

' This version does not require a second, slow variant named b
' Note the use of a "do-nothing" If - End If block :)

Private Sub UserForm_Initialize()
    Dim i As Long
    Dim a As Variant

    a = Range("A1:A" & Cells(Rows.Count, 1).End(xlUp).Row).Value

    With CreateObject("Scripting.Dictionary")
        For i = 1 To UBound(a)
            If .Item(a(i, 1)) Then
            End If
        Next i
        ComboBox1.List = .Keys
    End With
    
     
End Sub

 

by Skilled (401 points)

Thanks a lot ParseMonster. This is another version 

Private Sub UserForm_Initialize()
    Dim a, i As Long

    a = Range("A1:A" & Cells(Rows.Count, 1).End(xlUp).Row).Value

    With CreateObject("Scripting.Dictionary")
        For i = 1 To UBound(a)
            .Item(a(i, 1)) = Empty
        Next i
        ComboBox1.List = .Keys
    End With
End Sub

Less variables and no need to IF block too

Regards

by Skilled (265 points)
+2
Very cool! Well done.

 

Cheers,

 

Mitch

Welcome to wellsr Q&A
wellsr Q&A is the VBA and Python programming community that rewards you for learning how to code.

Earn free prizes for asking VBA and Python questions and for answering questions asked by others in our community.

What makes us different?
Our points system rewards you with a chance for free gifts based on the quality of your questions and/or answers. Users that ask great questions, may be given complimentary gift cards or training material.

Getting Started
Register

So, why don't you join us? It really is a neat way to reward the most dedicated members in our VBA and Python community.

For more programming tips visit the VBA Tutorials Blog and the Python Tutorials Blog.

...