Remove blank rows in a multicolumns ComboBox in VBA

Question

I'm having trouble to delete blank rows shown in my ComboBox and keeping my two columns. I tried this code:

  For Each c In Range(Range("A3"), Range("A" & Rows.Count).End(xlUp)) 
       If c.Value <> vbNullString Then ComboBox1.AddItem c.Value
  Next c

But it's just not working, Is there a way to change the Range for two columns? So there's my code with the parameters of the ComboBox.

Private Sub UserForm_Activate()

Dim c As Range

   With ComboBox1

     .ColumnCount = 2

     .ColumnWidths = "70;30"

     .ColumnHeads = False

     .BoundColumn = 1

     .List = Union(Range("A2:A100"), Range("B2:B100")).Value


  For Each c In Range(Range("A3"), Range("A" & Rows.Count).End(xlUp)) 
       If c.Value <> vbNullString Then ComboBox1.AddItem c.Value
  Next c

End With


End Sub

Thanks in advance

Answer 1

Try the following code...

Dim currentCell As Range

With Worksheets("Sheet1") 'change the sheet name accordingly
    With .Range("A2:A" & .Cells(.Rows.Count, "A").End(xlUp).Row)
        For Each currentCell In .Cells
            If Len(currentCell) > 0 Then
                With Me.ComboBox1
                    .AddItem currentCell.Value
                    .List(.ListCount - 1, 1) = currentCell.Offset(, 1).Value
                End With
            End If
        Next currentCell
    End With
End With

Hope this helps!