# countblank in a range made up of smaller areas

Occasional Contributor

# countblank in a range made up of smaller areas

If you have a range of cells (call it MyRange) made up of smaller areas, so that MyRange is never just one 'rectangle' of cells but a few of them (which happen to be adjoining), I find that the function countblank [counts the blank cells 'in a range'] with countblank(MyRange) throws up #Value! as the result, but with one area (equivalent to a rectangle of cells - see 'overallrange' in the attachment), you get the count of blank cells correctly shown in that kind of range.  Is there another way to get at the number of blank cells in a defined range (with range name), but where the range is made up of a number of smaller areas as such?  In the attachment, countblank(myrange) should state 27, but it states #value! instead.  Thanks for any help.

16 Replies

# Re: countblank in a range made up of smaller areas

You could use a custom function:

``````Function CountBlankAll(rng As Range) As Long
Dim n As Long
Dim area As Range
For Each area In rng.Areas
n = n + Application.CountBlank(area)
Next area
CountBlankAll = n
End Function``````

Use like this:

``=CountBlankAll(myrange)``

See the attached version. You'll have to allow macros.

# Re: countblank in a range made up of smaller areas

Hi, Hans,
That looks like it! Thanks very much, much appreciated.

Vincent

# Re: countblank in a range made up of smaller areas

In fact it is.  So thanks very much.

VIncent K

# Re: countblank in a range made up of smaller areas

Hi Hans,
I tried that and there's an extra factor that I should have mentioned: the 'myrange' I am using is made up of merged cells, each one 2 x 2. So what looks like 24 excel cells, would become 6. I think, therefore, that has made 'countblankall' miscalculate the nº of blank cells. I am hoping you might be able to modify the coding to allow for this, i.e. a 'collection' of 2x2 cells, adjoined, sort of criss-crossing - how to count the number of these that are blank. Thank you,
Vincent Kelly

# Re: countblank in a range made up of smaller areas

Your sample workbook didnt have merged cells...

Do you mean that you want an empty merged cell to count as 1? The built-in function COUNTBLANK does not do that.

But here is a new version. It will be slow for very large ranges.

``````Function CountBlankAll(rng As Range) As Long
Dim cel As Range
Dim dct As Object
Dim adr As String
Dim n As Long
Set dct = CreateObject("Scripting.Dictionary")
For Each cel In rng
If cel.MergeCells Then
Else
End If
If Not dct.Exists(adr) Then
If cel.Value = "" Then
n = n + 1
End If
End If
Next cel
CountBlankAll = n
End Function``````

# Re: countblank in a range made up of smaller areas

Hans, You know your stuff! Sorry about that omission (merged cells) - my mistake. Your amended function works perfectly! Thank you,
Vincent Kelly

# Re: countblank in a range made up of smaller areas

Sorry, Hans, one little problem, subsequent to my 5:08 am earlier reply. After a few minutes of having the file open & using this Function, I suddenly get an error message saying Code Execution has been Interrupted, and the highlighted line of code is line #11. Why would that be happening? Shall I send you the file in question?
VIncent K

# Re: countblank in a range made up of smaller areas

Yes, please attach the workbook.

# Re: countblank in a range made up of smaller areas

There are ways of dealing with this in Excel 365 insider beta; not exactly mainstream as yet!  Your 'little white dog' range comprises 6 areas.

The COUNTBLANK formula works on a single area at a time.  I don't think Excel will accept an array of areas so I have used the Lambda helper function MAP to address each in turn.  REDUCE does the same but accumulates a result as it goes.

# Re: countblank in a range made up of smaller areas

FIle attached here, Hans, with further description of what is happening . . .

As I say within the file, the 'crash' occurs unpredictably, so, if you just edit different cells with different numbers, there seems a good chance that, while yuo are doing that, it WILL occur & you will see for yourself what it might be due to.

Sorry for delay.

Thanks again,

Vincent

# Re: countblank in a range made up of smaller areas

I'm sorry, I have spent about 10 minutes ceaselessly entering, editing and deleting numbers in the puzzle cells. At no point did I get #VALUE!; the formulas returned the correct results all the time.

So I'm afraid I don't have an explanation, nor a solution.

# Re: countblank in a range made up of smaller areas

Yes, I was afraid that would happen. Keep the file for now, if you would, & I might get back with a particular sequence that brings up this anomaly. Thanks again. VK

# Re: countblank in a range made up of smaller areas

I'll keep the workbook.

# Re: countblank in a range made up of smaller areas

Hi, Hans,

Here's the file again, which I'll need to save to send to you, and, when you open it, the #Value! display will disappear, but I've taken some screenshots of the fact of the problem, for me.  I've added notes on the main page as well.  Thanks again for any help.  And it's a great function, excellent!

VIncent K

# Re: countblank in a range made up of smaller areas

HI, Peter, just saw your 3 lines of code, now,. Shall take a closer look & get back...
Vincent K

# Re: countblank in a range made up of smaller areas

It might be your laptop. I'm still not able to reproduce the error.