Two column VBA Combo Box populated from 2 different named-ranges

[keromero]

Is it possible to populate a two-column combobox populated from two different named ranges?

 

[jkpieterse]

Of course! A relatively easy way is to first populate a two-dimensional array with the values. Here is some air code:

Sub Demo()
    'Routine is in code module of userform
    Dim data() As String
    Dim rng1 As Range
    Dim rng2 As Range
    Dim rw As Long
    Dim cl As Range
    'Define which ranges, best to use range names instead of regular cell addresses
    Set rng1 = Range("NameForComboColumn0")
    Set rng2 = Range("NameForComboColumn1")
    ReDim data(1 To rng1.Rows.Count, 2)    'Set size of array. Assumes rng1 and rng2 have equal length!
    'Load values range 1
    For Each cl In rng1
        rw = rw + 1
        data(rw, 1) = cl.Value
    Next
    'Load values range 2
    rw = 0
    For Each cl In rng2
        rw = rw + 1
        data(rw, 2) = cl.Value
    Next
    'Fill listbox with data
    Me.Listbox1.List = data
End Sub

 

[keromero]

Hi Jan, thanks for your help. The code is working great. It helped me much...

Just two notes,

1) even though there are two columns, I have to run column count: "3" at combobox properties box.
2) Each time there is one "1" blank row at combo list

Thanks....

 

[jkpieterse]

You should not have to set the column count, it is handled by that list method. If there are empty rows there must be empty cells.