SOLVED

# @COUNTBLANK

Occasional Contributor

# @COUNTBLANK

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

# Re: @COUNTBLANK

It works for me.

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

``=COUNTBLANK(A1:C1)``

Result is 2.

# Re: @COUNTBLANK

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

# Re: @COUNTBLANK

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)"

# Re: @COUNTBLANK

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

# Re: @COUNTBLANK

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

# Re: @COUNTBLANK

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

# Re: @COUNTBLANK

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.

# Re: @COUNTBLANK

COUNTBLANK() is my intended use...

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

# Re: @COUNTBLANK

``=COUNTBLANK(B2:B18)``

Result is 8.

# Re: @COUNTBLANK

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