[DG3]

In excel, is there a way to count blank cells in a range, but count up to and stop the count when it reaches a cell with something in it. The COUNTBLANK function will count all of the blank cells in the range but does not stop the count upon reaching a non blank cell. Is there a way of doing this in excel?
Thanks,
Doug

 

[Glaswegian]

Hi Doug

I can do this with some code, if that's OK (Not so hot with formulae!). This should work - simply select the first blank cell, then run the code.

Sub CountSomeBlanks()
Dim myRng As Range

Set myRng = Range(ActiveCell, ActiveCell.End(xlDown))
MsgBox "Number of blanks cells: " & Application.WorksheetFunction.CountBlank(myRng), vbInformation, "Blank cell counter"
End Sub

Does this help?

Regards

 

[DG3]

Thanks for your help Iain. Is it possible to build a formula to do this?
Doug

 

[mgoldb2]

I have something that would work if the following conditions is ok with you

1. If you want it to stop counting at the first cell with text
2. That the first none blank is text not numeric (proberly a way to change this to numeric and not text by editing condition 1 of my formula)
3. the enitre range is in the same row

=MATCH("*",startvalue:endvalue,0)-1

Example

=MATCH("*",A3:A10,0)-1

A3 blank
A4 blank
A5 blank
A6 blank
A7 the
A8 blank

returns 4

Explanation of formula

Match find the spot in the range that meets a condition. if the range A5:A10 then A5 is spot 1 and A10 is spot 6.

condtion 1: item to find.
"*" is a trick to search for any text value
condition 2: range
condition 3: can be either -1,0,1 for what we doing with this command in this example it does not really matter.

There proberly a way to change condtion 1 to search for any numeric value instead of text but I dont know it off hand.