Aug 29 2021 08:28 AM
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.
Aug 29 2021 09:03 AM
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.
Aug 29 2021 09:09 AM
Aug 30 2021 10:08 AM
Sep 04 2021 02:57 PM
Sep 04 2021 03:28 PM
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
Sep 05 2021 05:08 AM
Sep 05 2021 06:35 AM
Sep 05 2021 07:05 AM
Yes, please attach the workbook.
Sep 05 2021 08:00 AM
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.
Sep 06 2021 10:12 AM
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
Sep 06 2021 10:43 AM
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.
Sep 06 2021 10:49 AM
Sep 06 2021 10:53 AM
I'll keep the workbook.
Sep 08 2021 11:17 AM
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
Sep 08 2021 11:19 AM
Sep 08 2021 12:31 PM
It might be your laptop. I'm still not able to reproduce the error.