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 funct...
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 Kelly
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
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 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- 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 KYes, please attach the workbook.
- Hans, You know your stuff! Sorry about that omission (merged cells) - my mistake. Your amended function works perfectly! Thank you,
Vincent Kelly