Using last row in this code

[wlknspc7]

Hello, I have an excel database with about 50 unique sheets. The formatting of these sheets is the same in all cases but each worksheet is for a different supplier, some worksheets contain 10 rows, some 450+
I made an userform, to make it easier to add, edit, etc. the products listed on these sheets.
This userform has a listbox that displays the content of these worksheets and I have a combobox that contains the names of the worksheets so I just have to click on the suppliers name on the combobox and it shows the proper worksheet I'm looking for
The problem is, using this range "!A5:P10000" does the job and shows the correct data from the worksheet but it also fills my listbox with endless blank rows after the rows with data, and no matter how I try I can't implement a lastrow range into RowSource
Is it possible to use a dynamic range here?

My code:

Private Sub ComboBox1_Click()
Dim ws As Worksheet

Set ws = Worksheets(Me.ComboBox1.Value)

With Me.ListBox1
.ColumnHeads = True
.RowSource = "A4:P4"
.ColumnCount = 16
.ColumnWidths = "20,0,0,60,120,120,200,40,40,40,40,40,40,40,40,120"
.RowSource = ws.Name & "!A5:P10000"
End With

End Sub

Thanks for the help in advance!

 

[Fluff]

How about

.RowSource = ws.Range("A5:P" & ws.Range("A" & Rows.Count).End(xlUp).Row).Address(, , , 1)

 

[wlknspc7]

How about

.RowSource = ws.Range("A5:P" & ws.Range("A" & Rows.Count).End(xlUp).Row).Address(, , , 1)

Works great, thank you!

 

[Fluff]

You're welcome & thanks for the feedback.