0 votes
in VBA by Beginner (115 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 (306 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 (115 points)
+1
Great and very swift solution. Thank you very much.
by Skilled (306 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 Beginner (204 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 (306 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 Beginner (204 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 as your contributions grow. The more points you earn, the better the prizes.

Getting Started
Register
Prizes

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

Top Users Mar 2019
  1. YasserKhalil

    306 Points

  2. ParserMonster

    204 Points

  3. Alberto Semat

    186 Points

  4. danmcg

    127 Points

  5. Abdan

    124 Points


Prizes for March 2019
Terms and Conditions
1st place (1) 1-year MyExcelOnline Academy Membership
(2) My First Add-in Dev Pack & Course
(3) AutoMacro: VBA Code Generator (Developer)
(4) VBA Cheat Sheet Bundle
(5) $35 Amazon Gift Card
2nd place (1) My First Add-in Dev Pack & Course
(2) 101 Most Popular Excel Formulas E-Book
(3) VBA Cheat Sheet Bundle
(4) $25 Amazon Gift Card
3rd place (1) Mouse to Macro
(2) VBA Cheat Sheet Bundle
(3) $15 Amazon Gift Card
...