Count of visible rows in a filtered range

**brucemc777** · 07-16-2014, 11:11 AM

Good Morning Folks!

I am trying to develop ways to make my spreadsheets interactive and am struggling in my learning of the employment of comboboxes, but that is incidental to all my other struggles...

From a combobox selection i filter a table for all entries containing the selected ID (from the combobox).

I first wanted to use selected columns from the resultant display (the filtered table) to populate another combobox so the user could drill down to the final selection that way, but seeing the mess i was getting involved in (I couldn't assign a range to the listfillrange of the other combobox) i think it might be best to settle for simply copying the visible cells to a new table on the selection page (the full database is on one sheet separate from the selection comboboxes and related controls), where the user can simply see the information needed on whatever line item they want - the number of filtered entries rarely exceeds five. What i can't understand is when i query the number of rows in the immediate window from the code snip below, it always comes back as "1", whether i do so on the full range or special visible cells -

Please Login or Register  to view this content.

To load another combobox i tired

Please Login or Register  to view this content.

(Quit laughing) and though i really would like to be able to do so for further refining, if not feasible, can work with just a display table (but if there is a relatively straight forward solution, i would love to learn from it!!!).

Querying the reultant rows i simply tried

Please Login or Register  to view this content.

and

Please Login or Register  to view this content.

. In both cases, though the filtered table had 5 records displayed, the count was... 1.

1) Can the visible cells resultant table be fed into a combobox relatively easily and if so, how?
2) With the count of rows, what am i doing wrong?

**romperstomper** · 07-16-2014, 11:40 AM

1. Loop and use AddItem or populate an array and assign that to the List property.
2. Rows.Count only returns the count of the rows in the first contiguous area - probably the header row in your case. You need to count the visible cells in one column only to get a count of visible rows.

**brucemc777** · 07-16-2014, 01:17 PM

I will try #1, and you are dead-on with #2.

Thanks- Highly "under-the-weather" today and didn't know how much sense i was making-

Will be back with results.

Edit: Ran the array into it and other than my lack of full understanding of how to control that dang combobox, all works as i originally hoped! Thank-you.