Prev: return 0 if cell has data?
Next: sum column based on single cell match?
From: Excel Ella on 9 Apr 2010 13:41
Is there a formula I can use to:
1. Count the number of merged cells (comprised of 3 cells) in a column AND
how many of those are blank?
2. Count the number of merged cells (comprised of 2 cells) in a column AND
howmany of those are blank?
From: Dave Peterson on 9 Apr 2010 14:37
Nothing built into excel. But you could use a macro.

It would look something like this:

Option Explicit
Function CountMergedCells(rng As Range, RowSize As Long, _
JustCountEmpty As Boolean) As Long

Dim myCell As Range
Dim TotalEmpty As Long
Dim TotalMergedCells As Long

Set rng = rng.Columns(1) 'single column

For Each myCell In rng.Cells
If myCell.MergeArea.Rows.Count = RowSize Then
If myCell.Address = myCell.MergeArea.Cells(1).Address Then
TotalMergedCells = TotalMergedCells + 1
If JustCountEmpty = True Then
If myCell.Cells(1).Value = "" Then
TotalEmpty = TotalEmpty + 1
End If
End If
End If
End If
Next myCell

If JustCountEmpty = True Then
CountMergedCells = TotalEmpty
Else
CountMergedCells = TotalMergedCells
End If

End Function

Then you could call it in code with something like:
Sub testme()

MsgBox CountMergedCells(ActiveSheet.Range("g1:G20"), _
RowSize:=3, JustCountEmpty:=True)

MsgBox CountMergedCells(ActiveSheet.Range("g1:G20"), _
RowSize:=2, JustCountEmpty:=True)

End Sub

Change the JustCountEmpty to False if you want the count of merged cells that
match the rowsize in the range.

If you wanted to call this function from a worksheet cell, you'd want to add a
line to the function:

Option Explicit
Function CountMergedCells(rng As Range, RowSize As Long, _
JustCountEmpty As Boolean) As Long

Application.Volatile '<-- added

Dim myCell As Range
Dim TotalEmpty As Long
Dim TotalMergedCells As Long

Set rng = rng.Columns(1) 'single column

For Each myCell In rng.Cells
If myCell.MergeArea.Rows.Count = RowSize Then
If myCell.Address = myCell.MergeArea.Cells(1).Address Then
TotalMergedCells = TotalMergedCells + 1
If JustCountEmpty = True Then
If myCell.Cells(1).Value = "" Then
TotalEmpty = TotalEmpty + 1
End If
End If
End If
End If
Next myCell

If JustCountEmpty = True Then
CountMergedCells = TotalEmpty
Else
CountMergedCells = TotalMergedCells
End If

End Function

And write the formula like:
=countmergedcells(g1:g20,3,true)

And DO NOT trust the results of this formula until you recalculate. Changing
the formatting of a cell doesn't cause excel to recalculate. So you'll want to
force a recalc (F9) before you trust the results.

But changing (clearing or adding a new value) to one of those cells in that
range should cause a recalc.




Excel Ella wrote:
>
> Is there a formula I can use to:
> 1. Count the number of merged cells (comprised of 3 cells) in a column AND
> how many of those are blank?
> 2. Count the number of merged cells (comprised of 2 cells) in a column AND
> howmany of those are blank?

--

Dave Peterson
From: Dave Peterson on 9 Apr 2010 14:38
Ps. As a general rule, I don't think it's a good idea to rely on formats
(merged cells or even colors or boldness or ...) as data.



Excel Ella wrote:
>
> Is there a formula I can use to:
> 1. Count the number of merged cells (comprised of 3 cells) in a column AND
> how many of those are blank?
> 2. Count the number of merged cells (comprised of 2 cells) in a column AND
> howmany of those are blank?

--

Dave Peterson
From: FSt1 on 9 Apr 2010 15:24
hi
see your post in eggheadcafe.

my advice. don't use merged cells. it may "look" good on the sheet but it
screws everything else up so i am at a lose as to why MS added this feature
in the first place.

regards
FSt1

"Excel Ella" wrote:

> Is there a formula I can use to:
> 1. Count the number of merged cells (comprised of 3 cells) in a column AND
> how many of those are blank?
> 2. Count the number of merged cells (comprised of 2 cells) in a column AND
> howmany of those are blank?
From: JLatham on 9 Apr 2010 17:34
I think it was a cave-in by the Excel development group to 'demands' made by
Excel users. Biggest problem (other than the real world headaches it causes
in worksheet calculation attempts like this or when referenced in macros) is
that the Help topic doesn't warn against the problems - should have told
folks to use it sparingly, and ONLY in cells containing labels that would
never be referenced anywhere else.

Excel Ella: I replied to your other post much the same as Dave Peterson has
here:
No worksheet functions that I know of to:
#1 - even figure out if a cell is a merged cell or not,
#2 - much less figure out how many cells have been merged together
#3 - merged cells referenced in calculations/macros = BAD
Recommendation: go back and take the time to unmerge them and use horizontal
alignment to "center across selection" so you can use them more easily in
worksheet formulas and macros.

"FSt1" wrote:

> hi
> see your post in eggheadcafe.
>
> my advice. don't use merged cells. it may "look" good on the sheet but it
> screws everything else up so i am at a lose as to why MS added this feature
> in the first place.
>
> regards
> FSt1
>
> "Excel Ella" wrote:
>
> > Is there a formula I can use to:
> > 1. Count the number of merged cells (comprised of 3 cells) in a column AND
> > how many of those are blank?
> > 2. Count the number of merged cells (comprised of 2 cells) in a column AND
> > howmany of those are blank?
 |  Next  |  Last
Pages: 1 2
Prev: return 0 if cell has data?
Next: sum column based on single cell match?