Count series in range

[rjhare]

I am writing an Excel function in VBA, but I am stuck on this:

I need to count the number of series in a range:

A column has a several ranges of numbers, split by blank cells:

1
0.5
1


1
1
1
1
1

1
1

I need to count the series of numbers, which in this case 3: (1, 0.5,1
and (1,1,1,1,1) and (1,1)

How do I do this from a passed range in VBA, please

Thank

 

[Tom Ogilvy]

rng.specialcells(xlconstants,xlnumbers).Areas.count

where rng represents the passed range.

 

[rjhare]

Thanks, but this did not work for me (XL2k on W2kSP4)

I have managed a workaround though.

Thanks to all those that viewe

 

[Tom Ogilvy]

Great, but
Just others are not deceived by your inability to implement and are not
forced to employ some cumbersome workaround

from the immediate window:

set rng = Columns(1)
? rng.specialcells(xlconstants,xlnumbers).Areas.count
8