Fill the combobox with the values of two different columns

[sofas]

Welcome. I am trying to fill a combobox with the values of two different columns. The following code does this, but I am having difficulty modifying it.
I want to fetch all column values up to the last row without selecting the last cell within the code, while ignoring the Empty cells if they are present in any column.

Private Sub UserForm_Initialize()
  Set f = Sheets("Sheet1")
  'Lastrow = f.Cells(f.Rows.Count, 1).End(xlUp).Row
  a = Application.Transpose(f.[a1:a10])
  b = Application.Transpose(f.[D1:D10])
  c = Split(Join(a, ",") & "," & Join(b, ","), ",")
  Me.ComboBox1.List = c
End Sub

 

[Fuji]

change

  a = Application.Transpose(f.[a1:a10])
  b = Application.Transpose(f.[D1:D10])

to

  a = Filter(f.[transpose(if(a1:a30000<>"",a1:a30000))], False, 0)
  b = Filter(f.[transpose(if(d1:d30000<>"",d1:d30000))], False, 0)

 

[sofas]

change

  a = Application.Transpose(f.[a1:a10])
  b = Application.Transpose(f.[D1:D10])

to

  a = Filter(f.[transpose(if(a1:a30000<>"",a1:a30000))], False, 0)
  b = Filter(f.[transpose(if(d1:d30000<>"",d1:d30000))], False, 0)

Very cool, this is what I was looking for. Thank you very much. I appreciate your help

 

[Artik]

Try:

Private Sub UserForm_Initialize()
    Dim Lastrow As Long
    Dim a As Variant, b As Variant, c As Variant
    Dim oDic As Object
    Dim i As Long

    Set oDic = CreateObject("Scripting.Dictionary")

    With Worksheets("Sheet1")
        Lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
        a = Application.Transpose(.Range("A1:A" & Lastrow).Value)

        Lastrow = .Cells(.Rows.Count, "D").End(xlUp).Row
        b = Application.Transpose(.Range("D1:D" & Lastrow).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
            oDic.Add i, c(i)
        End If
    Next i

    c = oDic.items()

    Me.ComboBox1.List = c
End Sub

Artik

 

[Fuji]

You are welcome and thanks for the feedback.

 

[sofas]

Try:

Private Sub UserForm_Initialize()
    Dim Lastrow As Long
    Dim a As Variant, b As Variant, c As Variant
    Dim oDic As Object
    Dim i As Long

    Set oDic = CreateObject("Scripting.Dictionary")

    With Worksheets("Sheet1")
        Lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
        a = Application.Transpose(.Range("A1:A" & Lastrow).Value)

        Lastrow = .Cells(.Rows.Count, "D").End(xlUp).Row
        b = Application.Transpose(.Range("D1:D" & Lastrow).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
            oDic.Add i, c(i)
        End If
    Next i

    c = oDic.items()

    Me.ComboBox1.List = c
End Sub

Artik

Thanks, I tried your code. It works very well and fulfills exactly what is required. But I think I will use the first code because it is somewhat short, especially since I need to add 6 columns or more. Thank you again.

 

[Artik]

I like Fuji's proposal, too.

Artik