Displaying the last used row in a userform listbox

chazrab

Well-known Member
Joined
Oct 21, 2006
Messages
605
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 ?
TOPOFLIST.jpg
BOTTOMOFLIST.jpg


Thanks for anyone's help. cr
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

chazrab

Well-known Member
Joined
Oct 21, 2006
Messages
605
Have a look at the TopIndex property...

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

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,826
Office Version
  1. 365
Platform
  1. Windows
To scroll to the last entry, try...

VBA Code:
    Me.ListBox1.TopIndex = Me.ListBox1.ListCount - 1

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

VBA Code:
    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.
 

Forum statistics

Threads
1,148,156
Messages
5,745,100
Members
423,923
Latest member
yuvaraj859

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top