Removing empty rows of combobox in excel - VBA

Question

This code show all cells column A of Sheet1 (Even empty values in end). What is the solution to Display up to the last row has a value in combobox1 (and remove empaty values in end)?

Private Sub UserForm_Initialize()
ComboBox1.List = Sheets("Sheet1").Range("A:A").Value
End Sub

Answer 1

Try this:

With Sheets("Sheet1")
    ComboBox1.List = .Range(.Range("A1"), .Cells(.Rows.count, 1).End(xlUp)).Value
End With