Populate Array for 2 Column Combobox Excluding empty - blank cells

[keromero]

I tried to populate an array for a "2 columns" combobox from named ranges but I had a lot of issues. So I do not like to use this option anymore. Is anyone can help me?

To populate

range1: "This workbook" "sheetname" "(A1:A20)"

And

range2: "This workbook" "sheetname" "(B1:B20)"

"And Ignoring and excluding any blank cells". So my combobox list items won't include any empty row.

the rest of the codes wikk follow as follows.

********************

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.ComboBox9.List = Data

**********************

Thanks very much....

 

[coulombevin]

Hi @keromero,

Here's a code that could help you. You must change combobox name and sheet name based on your needs:

Private Sub UserForm_Initialize()
    Dim myArr()
    Dim itemCount As Long, i As Long, c As Long
    'Change sheet name
    With Sheets("sheetDataX")
        itemCount = .Range("A1048576").End(xlUp).Row
        ReDim myArr(itemCount - 1, 2)
        c = 0
        i = 1
        For i = 1 To itemCount
            If .Range("A" & i).Value <> "" Then
                myArr(c, 1) = .Range("A" & i).Value
                myArr(c, 2) = .Range("B" & i).Value
                c = c + 1
            End If
        Next i
        'Change combobox name
        with ComboBox1
            .ColumnCount = 2
            i = 0
            For i = 0 To c - 1
                .AddItem myArr(i, 1)
                .Column(1, i) = myArr(i, 2)
            Next i
        End With
End Sub

Bests regards,

Vincent

 

[Fluff]

Duplicate to: populate an array for a "2 columns" combobox

In future, please do not post the same question multiple times. Per Forum Rules (#12), posts of a duplicate nature will be locked or deleted.

In relation to your question here, I have closed this thread so please continue in the linked thread. If you do not receive a response, you can "bump" it by replying to it yourself, though we advise you to wait 24 hours before doing so, and not to bump a thread more than once a day.