@COUNTBLANK

[Kenneth Rothschild]

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

[#2]

@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.

[#3]

@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

[#4 · Accepted 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)"

[#5]

I see a post from Hans and I will try that... Thank you...

[#6]

Hans - thank you for the post... I'm not so savvy about doing this... How do I implement this??? Thank you...

[#7]

@Kenneth Rothschild 

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

[#8]

@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.

[#9]

COUNTBLANK() is my intended use... ;)

[#10]

THANK YOU!!!
THANK YOU!!!
THANK YOU!!!

[#11]

@Kenneth Rothschild 

=COUNTBLANK(B2:B18)

Result is 8.

[#12]

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