What is the most efficient way to determine how many items are in a listbox? I have a userform with several listboxs and the contents of each is dynamic by the selection of another listbox. I have loops that rely on knowing the max number of items in each listbox. I tried for i = 1 to ubound(listbox2)....next i, but guess that doesn't work.
-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
Determine number of items in a listbox
- Thread starter sefMI
- Start date
Tom Urtis
MrExcel MVP
- Joined
- Feb 10, 2002
- Messages
- 11,300
The answer depends on how your listboxes are being populated.
Because you said they are dynamic, you can probably get what you want with the listcount property, example:
MsgBox ListBox1.ListCount
If your listboxes are being populated with their RowSource property, ListCount will give you a potentially false number, as it will represent the count of cells in the range, which can be higher than the actual items in the listbox if some cells in the rowsource range are empty. In this case you can loop through the items with a counter, but since you asked for the most efficient method, the loop option can be examined only after you say that you are using rowsource, which I am guessing you are not doing, given your eference to the dynamic nature of the userform.
Because you said they are dynamic, you can probably get what you want with the listcount property, example:
MsgBox ListBox1.ListCount
If your listboxes are being populated with their RowSource property, ListCount will give you a potentially false number, as it will represent the count of cells in the range, which can be higher than the actual items in the listbox if some cells in the rowsource range are empty. In this case you can loop through the items with a counter, but since you asked for the most efficient method, the loop option can be examined only after you say that you are using rowsource, which I am guessing you are not doing, given your eference to the dynamic nature of the userform.
Upvote
0
Similar threads
