0

Here is what I have so far that populates all the values from the cells into my combo box:

Private Sub UserForm_Initialize()

'Extracts Locations From Cells
Dim ws As Worksheet
Set ws = Worksheets("B")
Dim Location As Range
For Each Location In ws.Range("E7:E100")
    With Me.Combobox
        .AddItem Location.Value
    End With
Next Location

    
End Sub
Obadur
  • 1
  • For example using a `Scripting.Dictionary` as demonstrated [here](https://stackoverflow.com/questions/36044556/quicker-way-to-get-all-unique-values-of-a-column-in-vba). – BigBen May 17 '21 at 20:42
  • Thank you for responding. I've been trying to implement that one, but I am really new to VBA and can't seem to be able to incorporate it into my code. Would I have to Dim another range then do a For Each from this new range with unique values into my combo box? – Obadur May 18 '21 at 12:37
  • You should just be able to add the dictionary key to the combo box, without using a new range. – BigBen May 18 '21 at 12:43

1 Answers1

0

I found the answer in a different forum.

https://www.mrexcel.com/board/threads/vba-multi-column-dictionary-key-into-combobox.863507/

Private Sub UserForm_Activate()
            
            Application.ScreenUpdating = False

Dim rng As Range
Dim Dn As Range
Dim Dic As Object
      
Set rng = Range(Sheets("Subscription").Range("U2"), Sheets("Subscription").Range("U" & Rows.Count).End(xlUp))
    Set Dic = CreateObject("scripting.dictionary")
        Dic.CompareMode = vbTextCompare
For Each Dn In rng
  If Not Dn = vbNullString Then Dic(Dn.Value) = Empty
Next
With UserForm1.ComboBox1
    .Columncount = 2
    .RowSource = ""
    .List = Dic.Keys
    .ListIndex = 0
End With
Obadur
  • 1