Excel Function problem

DG3 · 09-25-2005, 04:48 PM

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** · 09-26-2005, 05:38 AM

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.

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 · 09-26-2005, 11:28 PM

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

**mgoldb2** · 09-27-2005, 01:45 AM

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.

09-25-2005, 04:48 PM	#1 (permalink)
DG3 Registered User Join Date: Jan 2005 Posts: 10 OS: XP	Excel Function problem 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

09-26-2005, 05:38 AM	#2 (permalink)
Glaswegian Moderator/ Rangemaster TSF Academy; Analyst, Security Team; Oor Wullie; TSF Surgeon and Resident Comic Join Date: Sep 2005 Location: Glasgow Posts: 25,482 OS: Win XP Pro SP3 / Win 7 Pro My System Blog Entries: 10	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. 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 __________________ Iain - Defender of the Haggis and all things Scottish. I don't help by PM - post in the Forums. PC Safety & Security::PC running a bit slow?::Donate::Photographers Corner

09-27-2005, 01:45 AM	#4 (permalink)
mgoldb2 TSF Enthusiast Join Date: Dec 2004 Posts: 604 OS: windows xp	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. __________________ Last edited by mgoldb2; 09-27-2005 at 01:47 AM.*

09-26-2005, 11:28 PM	#3 (permalink)
DG3 Registered User Join Date: Jan 2005 Posts: 10 OS: XP	Thanks for your help Iain. Is it possible to build a formula to do this? Doug