Aug 29 2021 08:28 AM
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:
See the attached version. You'll have to allow macros.
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 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.
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 08 2021 11:17 AM
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!
Sep 08 2021 11:19 AM