Snasui Excel Forum

Code: Select all

Private Sub UserForm_Initialize()
Dim LastRow As Integer
LastRow = Sheets("Sheet1").Cells(Cells.Rows.Count, "A").End(xlUp).Row
Me.ComboBox1.RowSource = "=Sheet1!A2:A" & LastRow
End Sub

Code: Select all

Private Sub UserForm_Initialize()
    Dim LastRow As Integer, i As Integer, _
        r As Range, rRng As Range, _
        cChoice As New Collection
    With Sheets("sheet1")
        LastRow = .Cells(Cells.Rows.Count, "A").End(xlUp).Row
        Set rRng = .Range("a2:a" & LastRow)
        On Error Resume Next
        For Each r In rRng
            cChoice.Add CStr(r.Value), CStr(r.Value)
        Next r
        On Error GoTo 0
    End With
    For i = 1 To cChoice.Count
        Me.ComboBox1.AddItem cChoice.Item(i)
    Next i
End Sub