Forum Discussion
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
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.
- HI, Peter, just saw your 3 lines of code, now,. Shall take a closer look & get back...
Vincent K
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 FunctionUse like this:
=CountBlankAll(myrange)See the attached version. You'll have to allow macros.
- 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 KellyYour 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 adr = cel.MergeArea.Address Else adr = cel.Address End If If Not dct.Exists(adr) Then dct.Add Item:=Null, Key:=adr If cel.Value = "" Then n = n + 1 End If End If Next cel CountBlankAll = n End Function
- Hi, Hans,
That looks like it! Thanks very much, much appreciated.
Vincent
