Counting Merged Cells
Excel Ella
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
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.
--
Dave Peterson
Dave Peterson
(merged cells or even colors or boldness or ...) as data.
--
Dave Peterson
FSt1
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
JLatham
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
i agree on all points. cave in syndrom is probably why we now have a million
rows and 16000 columns with people trying to use xl as a data base which i
have cautioned against. that is what access is for.
sigh.
regards
FSt1