Can't add over 9 items to a list item

[brucemc777]

I am evaluating rows in a table.

Going row by row, if a condition is met, I wish to first additem from the first column, then to that item add 12 additional cell values in the following 12 columns.

To eliminate any chance of it having to do with the nature of the cell value, I reduced the data to simple numerical, as shown below.

All runs fine for the first 10 items (the first by additem, then the next 9 by subsequent assignment as shown below) added to the listcount, but no matter what values I try for the next 3 it fails (on number 10). This was verified by when it failed and I selected debug I examined frmRelRecs.lbxRelRecs.List(0,9) in the immediate window and the value was 9.

Please Login or Register  to view this content.

I have my column count set to 13, even tried 20, but no difference.

Can someone please tell me what am I messing up on?

Many thanks!!!

p.s.: I was considering running an advanced filter on the table, copying it to a temp sheet and using the databody as my rowsource, but for some reason I chose this direction...

[AlphaFrog]

You're not crazy. There is a ten column limit using this method (adding column values individually).

Two methods around this limit:
1.) Use the RowSource method to assign a range to the the listbox
2.) Put your values in an array first and then assign the array to the listbox.

I imagine the 2nd option would work best or you. Let me know if you need help coding it.

[brucemc777]

Saving my sanity once again-

Thank-you!

[brucemc777]

AlphaFrog-

Do you have a rule-of-thumb guide as to when to use an advanced filter to a temp worksheet and use that UsedRange to populate a combobox/listbox rather than looping through the rows and adding? Seems to me that at some point the looping would take longer. Trying to wrap my head around the pros & cons of each method-

Thank-you!

[AlphaFrog]

1.) I almost never use the .RowSource method. To add\remove items in the list, you would have to add\remove items from the range. Too messy usually.

2.) I may use the .Add .Remove methods if the list is short. Coding is usually pretty easy, but you're right, it's not very efficient if the list is long and you have reference the data on sheet many times.

3.) My preferred method is to read the raw data into an array. Then loop through the array and filter out what I don't want. Then apply the array to the list (.List = MyArray). This an efficient method as it only requires one range reference to the worksheet to read the data en mas to an array. Looping through the data, once it's in an array, is very efficient.

Of course, all of that is just my preference.

[brucemc777]

I think I will kind of take the preference of a "Forum Guru" rather seriously...