VBA to count cells with data between blank rows

**Philcater** · 06-19-2012, 11:07 AM

Hi

I have a VBA that will count the data in column between blank rows. The problem i have is that the results are always placed at the bottom of each group. Which means when i use the remove duplicates tool i lose the data in in column B in the example below.

Data to grouped VBA Results
to be counted

abc
abc
abc 3

DEF 1

GHI
GHI 2

This is the VBA i am using

Please Login or Register  to view this content.

I either need to have the results at the top of each group or for the results to be put on every row.

Any ideas?

**Leith Ross** · 06-20-2012, 01:49 AM

Hello Philcater,

Welcome to the Forum!

This will place the count to right of the first element in the group.

Please Login or Register  to view this content.

**Marcol** · 06-20-2012, 02:53 AM

You can do this with a formula
In B1

Please Login or Register  to view this content.

Drag/Fill Down

If you must have VBa then this translates as ...

Please Login or Register  to view this content.

This might be faster than looping on larger ranges.

**Philcater** · 06-21-2012, 07:11 AM

Hi Thanks for the welcome. Leith Ross your code works perfectly and was just what i needed!

Thank you both for your help its going to save me so much time!