Find "Apple" in ColB, then count ColC until the next "Total" is found in ColB

I have several hundred rows of data. ColB contains search criteria, i.e. "Apple" for this example.

I want to search ColB for "Apple", then count non-empty cells in ColC until the next "Total" (there are many) is found in ColB. Below example would return 5. VBA preferable.


---------ColA-----ColB-----ColC
Row11------------Apple
Row12---------------------abc
Row13---------------------abc
Row14
Row15---------------------def
Row16---------------------def
Row17---------------------def

------------------Total

Thanks,
roothog

Try this:

Please Login or Register  to view this content.

Works perfectly! Thanks, Millz!!!

Now for a follow-up question...how can I put that value into a cell on the worksheet? Say, cell D1?

Please Login or Register  to view this content.

Works perfectly....again! Thanks for you time and expertise!

In using this I have found an anomaly I can't solve. What can I add in the event my search does not yield a result? In the above example, the macro stops at the "findrow" line if it doesn't find "Apple".

Thanks,
roothog

Here is an example, you can also apply it to the finding of "Total" if needed.

Please Login or Register  to view this content.

Thanks again, millz, for solving a second time. Works like a charm.
roothog