Excel 2013: Multicolumn Combobox Dynamic Range

[jedwardo]

Hi Everyone,

I have a userform combobox currently being populated with dynamic range "SameAs". At the moment it is populating the combobox with items from column A on sheet "Quantities" from row 6 down. How would I adjust the range to list items from column B as well? Here's what I'm using for column A:

=OFFSET(Quantities!$A$6, 0, 0, COUNTA(Quantities!$A:$A)-1,1)

I have multicolumn comboboxes working with static named ranges but can't seem to figure out how to tweak this dynamic named range to populate the combobox with 2 columns.

Thanks,
Jordan

 

[Norie]

Instead of using a dynamic named range you could use something like this, which would go in the userform's Initialize event.

With Sheets("Quantities")
    Set rngSameAs = .Range("A6", .Range("A" & Rows.Count).End(xlUp).Offset(,1))
End With

ComboBox1.List = rngSameAs.Value

If you still want to use the named range change the last parameter from 1 to 2.

=OFFSET(Quantities!$A$6, 0, 0, COUNTA(Quantities!$A:$A)-1,2)

 

[jedwardo]

Thanks Norie, that worked great!