Display all data in listview

[van0912]

Hello everyone,

I'm trying to display all data from my sheet to a listview in an userform. However, I can't get all rows but only a limit of ~50 in the listview. If I set "For i = 1 to 500" for example, I have a message error ("type mismatch"). I have the following code :

How can I get all row until last row?

Private Sub UserForm_Initialize() Dim ws As Worksheet Dim rg As Range Dim i As Long Dim j As Long Set ws = ThisWorkbook.Sheets("Transposition") Set rg = ws.Range("ReferenceTab") With UserForm1.ListView1 'get column 1 to 12 For i = 1 To 12 .ColumnHeaders.Add , , rg.Offset(0, i - 1) Next i 'get row 1 to 48 For i = 1 To 48 .ListItems.Add , , rg.Offset(i, 0) Next i For i = 1 To 48 For j = 1 To 11 .ListItems(i).ListSubItems.Add , , rg.Offset(i, j) Next j Next i .View = lvwReport End With End Sub

Thank you for your help!

 

[RoryA]

Works fine here. Are you sure you don't have any error values in your sheet?

 

[van0912]

Works fine here. Are you sure you don't have any error values in your sheet?

Hello Rory, thank you for replying!

Yees this code is working but a message error appears when i changed 48 to 500 saying there is a type mismatch... I tried to change but still get the error.
Dim i As Long to String
Dim j As Long to String

Do you know whats wrong?
Thanksss

 

[RoryA]

You didn't answer my question. Are there error values in the worksheet?

 

[van0912]

You didn't answer my question. Are there error values in the worksheet?

Oh yes you're right, there were 3 errors value! I've removed it and it's working now. Didn't know error value could impact the code!
Thank you so much for your help Rory!

 

[RoryA]

Glad we could help.