Set Range to Last Row With Data

[sparkytech]

I have a combobox that gets values autofilled and sorted from Column "A". Currently it looks at rows 15 to 80. How do I modify my code to search to the last row with data?

Dim myRange As Range


    Set myRange = Sheets("Sheet1").Range("A15:A80")
    With Sheets("Sheet1").Sort
        .SortFields.Clear
        .SortFields.Add myRange, xlSortOnValues, xlAscending
        .SetRange myRange
        .Apply
    End With
    
    Me.ComboBox1.List = myRange.value

 

[Michael M]

Try using

Dim myRange As Range, lr As Long
lr = Cells(Rows.Count, "A").End(xlUp).Row

    Set myRange = Sheets("Sheet1").Range("A15:A" & lr)
    With Sheets("Sheet1").Sort
        .SortFields.Clear
        .SortFields.Add myRange, xlSortOnValues, xlAscending
        .SetRange myRange
        .Apply
    End With
    
    Me.ComboBox1.List = myRange.Value
    End Sub