countblank in a range made up of smaller areas

%3CLINGO-SUB%20id%3D%22lingo-sub-2696749%22%20slang%3D%22en-US%22%3Ecountblank%20in%20a%20range%20made%20up%20of%20smaller%20areas%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2696749%22%20slang%3D%22en-US%22%3E%3CP%3EIf%20you%20have%20a%20range%20of%20cells%20(call%20it%20MyRange)%20made%20up%20of%20smaller%20areas%2C%20so%20that%20MyRange%20is%20never%20just%20one%20'rectangle'%20of%20cells%20but%20a%20few%20of%20them%20(which%20happen%20to%20be%20adjoining)%2C%20I%20find%20that%20the%20function%20countblank%20%5Bcounts%20the%20blank%20cells%20'in%20a%20range'%5D%20with%20countblank(MyRange)%20throws%20up%20%23Value!%20as%20the%20result%2C%20but%20with%20one%20area%20(equivalent%20to%20a%20rectangle%20of%20cells%20-%20see%20'overallrange'%20in%20the%20attachment)%2C%20you%20get%20the%20count%20of%20blank%20cells%20correctly%20shown%20in%20that%20kind%20of%20range.%26nbsp%3B%20Is%20there%20another%20way%20to%20get%20at%20the%20number%20of%20blank%20cells%20in%20a%20defined%20range%20(with%20range%20name)%2C%20but%20where%20the%20range%20is%20made%20up%20of%20a%20number%20of%20smaller%20areas%20as%20such%3F%26nbsp%3B%20In%20the%20attachment%2C%20countblank(myrange)%20should%20state%2027%2C%20but%20it%20states%20%23value!%20instead.%26nbsp%3B%20Thanks%20for%20any%20help.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2696749%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2696828%22%20slang%3D%22en-US%22%3ERe%3A%20countblank%20in%20a%20range%20made%20up%20of%20smaller%20areas%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2696828%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F42786%22%20target%3D%22_blank%22%3E%40Vincent%20Kelly%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20could%20use%20a%20custom%20function%3A%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-visual-basic%22%3E%3CCODE%3EFunction%20CountBlankAll(rng%20As%20Range)%20As%20Long%0A%20%20%20%20Dim%20n%20As%20Long%0A%20%20%20%20Dim%20area%20As%20Range%0A%20%20%20%20For%20Each%20area%20In%20rng.Areas%0A%20%20%20%20%20%20%20%20n%20%3D%20n%20%2B%20Application.CountBlank(area)%0A%20%20%20%20Next%20area%0A%20%20%20%20CountBlankAll%20%3D%20n%0AEnd%20Function%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3EUse%20like%20this%3A%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3DCountBlankAll(myrange)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3ESee%20the%20attached%20version.%20You'll%20have%20to%20allow%20macros.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2696841%22%20slang%3D%22en-US%22%3ERe%3A%20countblank%20in%20a%20range%20made%20up%20of%20smaller%20areas%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2696841%22%20slang%3D%22en-US%22%3EHi%2C%20Hans%2C%3CBR%20%2F%3EThat%20looks%20like%20it!%20Thanks%20very%20much%2C%20much%20appreciated.%3CBR%20%2F%3E%3CBR%20%2F%3EVincent%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2720776%22%20slang%3D%22en-US%22%3ERe%3A%20countblank%20in%20a%20range%20made%20up%20of%20smaller%20areas%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2720776%22%20slang%3D%22en-US%22%3EHi%20Hans%2C%3CBR%20%2F%3EI%20tried%20that%20and%20there's%20an%20extra%20factor%20that%20I%20should%20have%20mentioned%3A%20the%20'myrange'%20I%20am%20using%20is%20made%20up%20of%20merged%20cells%2C%20each%20one%202%20x%202.%20So%20what%20looks%20like%2024%20excel%20cells%2C%20would%20become%206.%20I%20think%2C%20therefore%2C%20that%20has%20made%20'countblankall'%20miscalculate%20the%20n%C2%BA%20of%20blank%20cells.%20I%20am%20hoping%20you%20might%20be%20able%20to%20modify%20the%20coding%20to%20allow%20for%20this%2C%20i.e.%20a%20'collection'%20of%202x2%20cells%2C%20adjoined%2C%20sort%20of%20criss-crossing%20-%20how%20to%20count%20the%20number%20of%20these%20that%20are%20blank.%20Thank%20you%2C%3CBR%20%2F%3EVincent%20Kelly%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2721491%22%20slang%3D%22en-US%22%3ERe%3A%20countblank%20in%20a%20range%20made%20up%20of%20smaller%20areas%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2721491%22%20slang%3D%22en-US%22%3EHans%2C%20You%20know%20your%20stuff!%20Sorry%20about%20that%20omission%20(merged%20cells)%20-%20my%20mistake.%20Your%20amended%20function%20works%20perfectly!%20Thank%20you%2C%3CBR%20%2F%3EVincent%20Kelly%3C%2FLINGO-BODY%3E
Occasional Contributor

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

@Vincent Kelly 

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.

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

Vincent

@Vincent Kelly 

 

In fact it is.  So thanks very much.

 

VIncent K

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

@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

 

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

@Vincent Kelly 

Yes, please attach the workbook.

@Vincent Kelly 

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.

image.png

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.

@Hans Vogelaar 

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

 

@Vincent Kelly 

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.

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

@Vincent Kelly 

I'll keep the workbook.

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

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

@Vincent Kelly 

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