COUNTBLANK and merged cells

[igor]

I am trying to count blank cells in a spreadsheet, and the COUNTBLANK
function returns merged cells as blank apart from the left-most,
upper-most cell.
Is there a way to count all cells in a merged cell as having content,
and not just the first one, so that they are not counted as blank in
the COUNTBLANK function?
Any help would be very much appreciated.



------------------------------------------------




------------------------------------------------

 

[Myrna Larson]

As others have said on many occasions, merged cells seem to create more problems than they
solve. I think you've just come up with another example.

If you are merging cells to accomplish centering a header over several columns, I suggest you
use the CenterAcrossSelection alignment.

Otherwise, insert blank columns so the long text can spill to the right.

 

[Harlan Grove]

I am trying to count blank cells in a spreadsheet, and the COUNTBLANK
function returns merged cells as blank apart from the left-most,
upper-most cell.
Is there a way to count all cells in a merged cell as having content,
and not just the first one, so that they are not counted as blank in
the COUNTBLANK function?

Not without a UDF (user-defined function).


Function foo(rng As Range) As Long
Dim c As Range, skip As Range

For Each c In rng

If skip Is Nothing Then

If c.Formula = "" Then foo = foo + 1
If c.MergeCells Then Set skip = c.MergeArea

ElseIf Intersect(c, skip) Is Nothing Then

If c.Formula = "" Then foo = foo + 1
If c.MergeCells Then Set skip = Union(skip, c.MergeArea)

End If

Next c

End Function


In general, merged cells should be used as little as possible. Formulas based on
ranges are not the only bits of Excel functionality thoroughly screwed up by
merged cells. Without a doubt, one of the most problematic features in Excel.