Count Rows Until Cell Value or Cell Value Changes

[STIguy]

Experts,

I'm stuck on a custom sort. I have converted the data into a tiered format, but now I would like to go through the "Level" column and determine the number of Level 3 entries in each group. For the following data I would like to return the number of rows in each Level 3 group. A named integer would be perfect.

L2item Group 1 - 5 rows of Level 3 items
L2item Group 2 - 4 rows of Level 3 items
L2item Group 3 - 3 rows of Level 3 items

Any advice is greatly appreciated!

Data set:

[TABLE="width: 321"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]Level[/TD]
[TD]L2 Item[/TD]
[TD]PN[/TD]
[TD]Description[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD] [/TD]
[TD]1000[/TD]
[TD]Truck[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]1[/TD]
[TD]101[/TD]
[TD]Apple[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]1[/TD]
[TD]501[/TD]
[TD]Dog[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]1[/TD]
[TD]502[/TD]
[TD]Cat[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]1[/TD]
[TD]503[/TD]
[TD]Snake[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]1[/TD]
[TD]504[/TD]
[TD]Shark[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]1[/TD]
[TD]506[/TD]
[TD]Tiger[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]2[/TD]
[TD]102[/TD]
[TD]Orange[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]2[/TD]
[TD]601[/TD]
[TD]Spider[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]2[/TD]
[TD]602[/TD]
[TD]Zebra[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]3[/TD]
[TD]103[/TD]
[TD]Banana[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]3[/TD]
[TD]701[/TD]
[TD]Fish[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]3[/TD]
[TD]703[/TD]
[TD]Pig[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]3[/TD]
[TD]704[/TD]
[TD]Cougar[/TD]
[/TR]
</tbody>[/TABLE]

 

[STIguy]

ah, I see now. That's pretty fancy. I'm a VBA newb, so I am used to doing things with an ugly brute force method. Now out of curiosity along those lines...is it possible to create a variable that stores a list in which the contents of that list are populated from multiple non-consecutive ranges?

 

[STIguy]

I didn't want to hijack my own thread, so I created a new one under: http://www.mrexcel.com/forum/excel-...y-unique-values-visible-only.html#post4015894

I believe code similar to the code mumps provided here could resolve this scenario.