populate an array for a "2 columns" combobox

[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....

 

[sofas]

Try this

Private Sub UserForm_Initialize()
    Dim a As Variant, b As Variant, c As Variant
    Dim tbl As Object, i As Long
    Dim lr As Long
    
    Set tbl = CreateObject("Scripting.Dictionary")
    
    With Worksheets("sheetname")
        lr = .Cells(.Rows.Count, "B").End(xlUp).Row
        a = Application.Transpose(.Range("A1:A" & lr).Value)
        b = Application.Transpose(.Range("B1:B" & lr).Value)
    End With

    c = Split(Join(a, ",") & "," & Join(b, ","), ",")
    
    On Error Resume Next
    For i = 0 To UBound(c)
        If Len(c(i)) > 0 Then
            tbl(c(i)) = c(i)
        End If
    Next i

    c = tbl.items()
    Me.ComboBox9.List = c
End Sub

 

[hungtbatman1]

Private Sub UserForm_Initialize()
Dim i As Long, count As Long, data(), result()
    data = Worksheets("sheetname").Range("A1:B20").Value
    ReDim result(1 To 2, 1 To UBound(data, 1))
    For i = 1 To UBound(data, 1)
        If Len(data(i, 1)) > 0 And Len(data(i, 2)) > 0 Then
            count = count + 1
            result(1, count) = data(i, 1)
            result(2, count) = data(i, 2)
        End If
    Next i
    ReDim Preserve result(1 To 2, 1 To count)
    Me.ComboBox9.ColumnCount = 2
    Me.ComboBox9.Column = result
End Sub

 

[Fuji]

    Dim a, b, ub&
    a = Filter(range1.Parent.Evaluate(Replace("transpose(if(#<>"""",#))", "#", range1.Address)), False, 0)
    b = Filter(range2.Parent.Evaluate(Replace("transpose(if(#<>"""",#))", "#", range2.Address)), False, 0)
    If (UBound(a) = -1) + (UBound(b) = -1) Then Exit Sub
    ub = Application.Min(UBound(a), UBound(b))
    ReDim Preserve a(ub), b(ub)
    a = Application.Transpose(Array(a, b))
    If ub > 0 Then
        Me.ComboBox1.List = a
    Else
        Me.ComboBox1.Column = a
    End If

The above currently adopt to less row(s) when rows have different length.
If you want it other way round, change Min to Max.

 

[keromero]

Try this

Private Sub UserForm_Initialize()
    Dim a As Variant, b As Variant, c As Variant
    Dim tbl As Object, i As Long
    Dim lr As Long
   
    Set tbl = CreateObject("Scripting.Dictionary")
   
    With Worksheets("sheetname")
        lr = .Cells(.Rows.Count, "B").End(xlUp).Row
        a = Application.Transpose(.Range("A1:A" & lr).Value)
        b = Application.Transpose(.Range("B1:B" & lr).Value)
    End With

    c = Split(Join(a, ",") & "," & Join(b, ","), ",")
   
    On Error Resume Next
    For i = 0 To UBound(c)
        If Len(c(i)) > 0 Then
            tbl(c(i)) = c(i)
        End If
    Next i

    c = tbl.items()
    Me.ComboBox9.List = c
End Sub

Hi Sofas, thanks for the code. Actually code is working very well however it does not serve my need.

Your code combines Range A & Range B into one combo box list. However what I need is to populate a two column combobox list as

Range A into Combobox column 1
Range B into Combobox column 2

Anyways I respect the time you sent to help me. Thank you.

 

[keromero]

Private Sub UserForm_Initialize()
Dim i As Long, count As Long, data(), result()
    data = Worksheets("sheetname").Range("A1:B20").Value
    ReDim result(1 To 2, 1 To UBound(data, 1))
    For i = 1 To UBound(data, 1)
        If Len(data(i, 1)) > 0 And Len(data(i, 2)) > 0 Then
            count = count + 1
            result(1, count) = data(i, 1)
            result(2, count) = data(i, 2)
        End If
    Next i
    ReDim Preserve result(1 To 2, 1 To count)
    Me.ComboBox9.ColumnCount = 2
    Me.ComboBox9.Column = result
End Sub

Hi Hungtbatman1

Thanks for the code and your help. This is exactly what I needed and the code is working flawlessly

 

[keromero]

    Dim a, b, ub&
    a = Filter(range1.Parent.Evaluate(Replace("transpose(if(#<>"""",#))", "#", range1.Address)), False, 0)
    b = Filter(range2.Parent.Evaluate(Replace("transpose(if(#<>"""",#))", "#", range2.Address)), False, 0)
    If (UBound(a) = -1) + (UBound(b) = -1) Then Exit Sub
    ub = Application.Min(UBound(a), UBound(b))
    ReDim Preserve a(ub), b(ub)
    a = Application.Transpose(Array(a, b))
    If ub > 0 Then
        Me.ComboBox1.List = a
    Else
        Me.ComboBox1.Column = a
    End If

The above currently adopt to less row(s) when rows have different length.
If you want it other way round, change Min to Max.

Hİ Fuji thanks for the above code. Actually for my level, above code seems complicated to me. Can you please define what to change above to set Range A1:A20 and RAnge B1:B20
If you assist to help to adapt above code to my data it would be helpful.

For instance what should I cahnge here:
a = Filter(range1.Parent.Evaluate(Replace("transpose(if(#<>"""",#))", "#", range1.Address)), False, 0)

Thanks and Best Regards,

 

[Fuji]

No need to change any, just set the reference to the range like in your initial post.
I thought range1 and range 2 would be in different worksheets, but anyway...
Replace YourSheetNameHere to actual sheet name(s).

Private Sub UserForm_Initialize()
    Dim range1 As Range, range2 As Range, a, b, ub&
    Set range1 = Sheets("YourSheetNameHere").Range("A1:A20")
    Set range2 = Sheets("YourSheetNameHere").Range("B1:B20")
    a = Filter(range1.Parent.Evaluate(Replace("transpose(if(#<>"""",#))", "#", range1.Address)), False, 0)
    b = Filter(range2.Parent.Evaluate(Replace("transpose(if(#<>"""",#))", "#", range2.Address)), False, 0)
    If (UBound(a) = -1) + (UBound(b) = -1) Then Exit Sub
    ub = Application.Min(UBound(a), UBound(b))
    ReDim Preserve a(ub), b(ub)
    a = Application.Transpose(Array(a, b))
    If ub > 0 Then
        Me.ComboBox1.List = a
    Else
        Me.ComboBox1.Column = a
    End If
End Sub

 

[sofas]

Hi Sofas, thanks for the code. Actually code is working very well however it does not serve my need.

Your code combines Range A & Range B into one combo box list. However what I need is to populate a two column combobox list as

Range A into Combobox column 1
Range B into Combobox column 2

Anyways I respect the time you sent to help me. Thank you.

This was what I understood from your question, unfortunately....
To fill in the list and display each column separately, it is possible to modify the previous code

Private Sub UserForm_Initialize()
    Dim i As Long, count As Long, tmp() As Variant, ColA As Object, ColB As Object
    Dim Rng1 As Range, Rng2 As Range, a As Variant, b As Variant

    Set Rng1 = Sheets("sheetname").Range("A1:A20")
    Set Rng2 = Sheets("sheetname").Range("B1:B20")
    a = Rng1.value: b = Rng2.value

    Set ColA = CreateObject("Scripting.Dictionary"): Set ColB = CreateObject("Scripting.Dictionary")

    For i = 1 To UBound(a, 1): If Len(a(i, 1)) > 0 And Not ColA.Exists(a(i, 1)) Then ColA.Add a(i, 1), a(i, 1)
    Next i

    For i = 1 To UBound(b, 1): If Len(b(i, 1)) > 0 And Not ColB.Exists(b(i, 1)) Then ColB.Add b(i, 1), b(i, 1)
    Next i

    count = Application.WorksheetFunction.Max(ColA.count, ColB.count)
    ReDim tmp(1 To count, 1 To 2)

    i = 1: For Each Key In ColA.Keys: tmp(i, 1) = Key: i = i + 1: Next Key
    i = 1: For Each Key In ColB.Keys: tmp(i, 2) = Key: i = i + 1: Next Key

    Me.ComboBox9.ColumnCount = 2
    Me.ComboBox9.List = tmp
End Sub

 

[coulombevin]

Hi @keromero ,

Here's a code that would work for you (i posted also in your duplicated thread):

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