SOLVED

@COUNTBLANK

%3CLINGO-SUB%20id%3D%22lingo-sub-2369022%22%20slang%3D%22en-US%22%3E%40COUNTBLANK%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2369022%22%20slang%3D%22en-US%22%3E%3CP%3EThe%20%40countblank%20function%20is%20not%20helping%20me%20%3CLI-EMOJI%20id%3D%22lia_disappointed-face%22%20title%3D%22%3Adisappointed_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESome%20of%20the%20cells%20are%20actually%20merged%20cells...'%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFor%20example%2C%20some%20cells%20are%20only%20one%20cell%20long%2C%20and%20other%20cells%20are%20merged%20-%20spanning%205%20cells.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20that%20merged%205%20cells%2C%20only%20one%20cell%20has%20data%20while%20the%20other%204%20cells%20are%20blank.%26nbsp%3B%20Nothing%20is%20consistent%20in%20the%20layout%20I%20have%20as%20there%20are%20some%20merged%20cells%20that%20are%202%2C%203%2C%204%2C%205%2C%20or%206%20cells%20and%20most%20of%20them%20have%20only%20one%20data%20entry.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%26nbsp%3B%40COUNTBLANK%20cell%20counts%20%3CSTRONG%3EALL%3C%2FSTRONG%3E%20of%20the%20cells%20that%20does%20not%20have%20data%20whereas%20I%20only%20want%20that%20merged%20cell%20(with%20only%20one%20data%20entry%20in%20it)%20not%20to%20be%20counted%20as%20a%20blank%20cell.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20do%20understand%20that%20the%20merged%20cells%20are%20still%20taking%20up%20the%20row%20or%20column%20(whatever%20it%20is%20applicable)%2C%20but%20I%20want%20it%20to%20be%20counted%20as%20a%20non-blank%20if%20there%20is%20data.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhat%20can%20I%20do%20or%20is%20there%20another%20function%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EKenneth%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2369022%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2369078%22%20slang%3D%22en-US%22%3ERe%3A%20%40COUNTBLANK%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2369078%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F186361%22%20target%3D%22_blank%22%3E%40Kenneth%20Rothschild%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt%20works%20for%20me.%3C%2FP%3E%3CP%3EA1%20has%20a%20value.%20B1%20and%20C1%20are%20empty.%20A1%3AC1%20are%20merged.%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DCOUNTBLANK(A1%3AC1)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3EResult%20is%202.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2369137%22%20slang%3D%22en-US%22%3ERe%3A%20%40COUNTBLANK%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2369137%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F186361%22%20target%3D%22_blank%22%3E%40Kenneth%20Rothschild%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20have%20found%20one%20of%20the%20many%2C%20many%20reasons%20why%20using%20merged%20cells%20is%20a%20bad%20idea...%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20I%20interpret%20your%20question%20correctly%2C%20you'd%20need%20a%20custom%20function.%20The%20following%20is%20based%20on%20an%20idea%20of%20Harlan%20Grove%3A%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-visual%22%3E%3CCODE%3EFunction%20RealBlank(rng%20As%20Range)%20As%20Long%0A%20%20%20%20Dim%20c%20As%20Range%0A%20%20%20%20Dim%20skip%20As%20Range%0A%20%20%20%20Application.Volatile%0A%20%20%20%20For%20Each%20c%20In%20rng%0A%20%20%20%20%20%20%20%20If%20skip%20Is%20Nothing%20Then%0A%20%20%20%20%20%20%20%20%20%20%20%20If%20c.Value%20%3D%20%22%22%20Then%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20RealBlank%20%3D%20RealBlank%20%2B%201%0A%20%20%20%20%20%20%20%20%20%20%20%20End%20If%0A%20%20%20%20%20%20%20%20%20%20%20%20If%20c.MergeCells%20Then%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20Set%20skip%20%3D%20c.MergeArea%0A%20%20%20%20%20%20%20%20%20%20%20%20End%20If%0A%20%20%20%20%20%20%20%20ElseIf%20Intersect(c%2C%20skip)%20Is%20Nothing%20Then%0A%20%20%20%20%20%20%20%20%20%20%20%20If%20c.Value%20%3D%20%22%22%20Then%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20RealBlank%20%3D%20RealBlank%20%2B%201%0A%20%20%20%20%20%20%20%20%20%20%20%20End%20If%0A%20%20%20%20%20%20%20%20%20%20%20%20If%20c.MergeCells%20Then%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20Set%20skip%20%3D%20Union(skip%2C%20c.MergeArea)%0A%20%20%20%20%20%20%20%20%20%20%20%20End%20If%0A%20%20%20%20%20%20%20%20End%20If%0A%20%20%20%20Next%20c%0AEnd%20Function%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2369230%22%20slang%3D%22en-US%22%3ERe%3A%20%40COUNTBLANK%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2369230%22%20slang%3D%22en-US%22%3EI%20don't%20understand%20why%20it%20worked%20for%20you..%20but%20here%20is%20what%20I%20have%20encountered%20(an%20example)%3CBR%20%2F%3E%3CBR%20%2F%3E%3CBR%20%2F%3EB2%204-May%3CBR%20%2F%3EB3%205%2F3%2F2021%3CBR%20%2F%3EB4%3CBR%20%2F%3EB5%3CBR%20%2F%3EB6%204-May%3CBR%20%2F%3EB7%3CBR%20%2F%3EB8%205-May%3CBR%20%2F%3EB9%206-May%3CBR%20%2F%3EB10%207-May%3CBR%20%2F%3EB11%3CBR%20%2F%3EB12%3CBR%20%2F%3EB13%208-May%3CBR%20%2F%3EB14%209-May%3CBR%20%2F%3EB15%3CBR%20%2F%3EB16%3CBR%20%2F%3EB17%3CBR%20%2F%3EB18%2010-May%3CBR%20%2F%3E%3CBR%20%2F%3E%3CBR%20%2F%3E9%20%22%3DCOUNT(B2%3AB18)%22%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2369234%22%20slang%3D%22en-US%22%3ERe%3A%20%40COUNTBLANK%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2369234%22%20slang%3D%22en-US%22%3EI%20see%20a%20post%20from%20Hans%20and%20I%20will%20try%20that...%20Thank%20you...%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2369242%22%20slang%3D%22en-US%22%3ERe%3A%20%40COUNTBLANK%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2369242%22%20slang%3D%22en-US%22%3EHans%20-%20thank%20you%20for%20the%20post...%20I'm%20not%20so%20savvy%20about%20doing%20this...%20How%20do%20I%20implement%20this%3F%3F%3F%20Thank%20you...%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2369356%22%20slang%3D%22en-US%22%3ERe%3A%20%40COUNTBLANK%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2369356%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F186361%22%20target%3D%22_blank%22%3E%40Kenneth%20Rothschild%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAre%20you%20talking%20about%20COUNT()%20or%20COUNTBLANK()%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

The @countblank function is not helping me

 

Some of the cells are actually merged cells...'

 

For example, some cells are only one cell long, and other cells are merged - spanning 5 cells.

 

In that merged 5 cells, only one cell has data while the other 4 cells are blank.  Nothing is consistent in the layout I have as there are some merged cells that are 2, 3, 4, 5, or 6 cells and most of them have only one data entry.

 

The @COUNTBLANK cell counts ALL of the cells that does not have data whereas I only want that merged cell (with only one data entry in it) not to be counted as a blank cell.

 

I do understand that the merged cells are still taking up the row or column (whatever it is applicable), but I want it to be counted as a non-blank if there is data.

 

What can I do or is there another function?

 

Thank you,

 

Kenneth

11 Replies

@Kenneth Rothschild 

It works for me.

A1 has a value. B1 and C1 are empty. A1:C1 are merged.

=COUNTBLANK(A1:C1)

Result is 2.

 

@Kenneth Rothschild 

You have found one of the many, many reasons why using merged cells is a bad idea...

 

If I interpret your question correctly, you'd need a custom function. The following is based on an idea of Harlan Grove:

Function RealBlank(rng As Range) As Long
    Dim c As Range
    Dim skip As Range
    Application.Volatile
    For Each c In rng
        If skip Is Nothing Then
            If c.Value = "" Then
                RealBlank = RealBlank + 1
            End If
            If c.MergeCells Then
                Set skip = c.MergeArea
            End If
        ElseIf Intersect(c, skip) Is Nothing Then
            If c.Value = "" Then
                RealBlank = RealBlank + 1
            End If
            If c.MergeCells Then
                Set skip = Union(skip, c.MergeArea)
            End If
        End If
    Next c
End Function
best response confirmed by Kenneth Rothschild (Occasional Contributor)
Solution
I don't understand why it worked for you.. but here is what I have encountered (an example)


B2 4-May
B3 5/3/2021
B4
B5
B6 4-May
B7
B8 5-May
B9 6-May
B10 7-May
B11
B12
B13 8-May
B14 9-May
B15
B16
B17
B18 10-May


9 "=COUNT(B2:B18)"
I see a post from Hans and I will try that... Thank you...
Hans - thank you for the post... I'm not so savvy about doing this... How do I implement this??? Thank you...

@Kenneth Rothschild 

Are you talking about COUNT() or COUNTBLANK()?

 

@Kenneth Rothschild

Press Alt+F11 to activate the Visual Basic Editor.

Select Insert > Module to create a new code module.

Copy the code from my previous reply into the module.

Switch back to Excel.

To count the number of blank cells in A1:D10, counting a blank merged cell as one, use

 

=RealBlank(A1:D10)

 

Save the workbook as a macro-enabled workbook (*.xlsm).

Make sure that you allow macros when you open the workbook.

COUNTBLANK() is my intended use...
THANK YOU!!!
THANK YOU!!!
THANK YOU!!!

@Kenneth Rothschild 

=COUNTBLANK(B2:B18)

Result is 8.

 

It is not working for me.. I was able to do the RealBlank function from @Hans Vogelaar and it worked as per my needs!!!