May 19 2021 10:12 AM
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
May 19 2021 10:25 AM
It works for me.
A1 has a value. B1 and C1 are empty. A1:C1 are merged.
=COUNTBLANK(A1:C1)
Result is 2.
May 19 2021 10:40 AM
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
May 19 2021 10:51 AM
SolutionMay 19 2021 10:53 AM
May 19 2021 11:04 AM
May 19 2021 11:09 AM
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.
May 19 2021 11:31 AM
May 19 2021 10:51 AM
Solution