VBA Multi Column Dictionary Key into Combobox

[neb255]

Hi,

I have the below snippet of code which is creating a unique list for a combobox on a form. It works very well to provide me the unique list of values from column "U," however I would like to include the associated Values from column "M." I have read through a number of posts on this topic but cant seem to get it to work for me.

I dont know if this is possible since I need the selected value from the userform combobox to be used as a filter on column "U" in a later section of code.

Can someone please help me revise this?

I appreciate any help

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

Here is the later code which uses the combobox selection as a filter incase this makes a difference:

Dim lasta As Long
lasta = Sheets("Subscription").Range("a" & Rows.Count).End(xlUp).Row

    Range("A1:U" & lasta).AutoFilter Field:=21, Criteria1:=UserForm1.ComboBox1.Value

 

[V_Malkoti]

How should values from M be included in the combobox? Like "U1 - M1", "U2 - M2", ... , "Un - Mn" or U1, U2, ..., Un, M1, M2, ... , Mn?

 

[neb255]

Hi, sorry I forgot to include that. Ideally it would be U1-M1, U2-M2

Thanks

 

[V_Malkoti]

You need to update the loop in UserForm_Activate:

For Each Dn In rng
      If Not Dn = vbNullString Then Dic([COLOR=#0000ff]Dn.Value & "-" & Sheets("Subscription").Range("M" & Dn.Row)[/COLOR]) = Empty
Next

And then later retrieve column U values from it as:

Range("A1:U" & lasta).AutoFilter Field:=21, Criteria1:=[COLOR=#0000ff]Split(UserForm1.ComboBox1.Value, "-")(0)[/COLOR]

 

[neb255]

Thanks A Million!!!!

That was exactly what i was looking for.

Cheers!