Displaying the last used row in a userform listbox

[chazrab]

A YTD report generates 152 items. The userform Listbox1 always displays the beginning of the data with a scrollbar. Fine, no problem
But I have to manually scroll down to row 153 to be able to view the total amount spent in a given column. How would you tell Excel
to just move to the bottom of the list and display it in Listbox1 as shown in the 2nd image below ?

Thanks for anyone's help. cr

 

[Domenic]

Have a look at the TopIndex property...

TopIndex property (Microsoft Forms)

Office VBA reference topic

docs.microsoft.com

 

[chazrab]

Have a look at the TopIndex property...

TopIndex property (Microsoft Forms)

Office VBA reference topic

docs.microsoft.com

Thanks, but unless I'm not understanding the TopIndex property, all that does is display the top row in a list

"Sets and returns the item that appears in the topmost position in the list."

As mentioned, the solution is the 2nd image - displaying the row with the total at the BOTTOM of the list.
Listbox reports with rows greater than what the userform listbox dims can display will never show the bottom.
All I'm after is an automated way to have the last row or bottom used row which shows the total amount display in the center, more less of the listbox
without having to manually scoll to the bottom.

Sorry if I wasn't clear. I thought both images would self explanatory.

cr

 

[Domenic]

To scroll to the last entry, try...

    Me.ListBox1.TopIndex = Me.ListBox1.ListCount - 1

Or, the same thing, but using the With/End With statement...

    With Me.ListBox1
        .TopIndex = .ListCount - 1
    End With

However, it looks like you have a number of blank rows. If so, you may need to adjust the number you assign to TopIndex.

 

[SUPERDAD]

A YTD report generates 152 items. The userform Listbox1 always displays the beginning of the data with a scrollbar. Fine, no problem
But I have to manually scroll down to row 153 to be able to view the total amount spent in a given column. How would you tell Excel
to just move to the bottom of the list and display it in Listbox1 as shown in the 2nd image below ? View attachment 38422View attachment 38423

Thanks for anyone's help. cr

Try this one bro

Private Sub UserForm_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
With Me.ListBox1
.TopIndex = .ListCount - 620
End With
End Sub

 

[SUPERDAD]

Try this one bro

Private Sub UserForm_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
With Me.ListBox1
.TopIndex = .ListCount - 620
End With
End Sub

you need to adjust the list count number to show your desired display