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 cavein 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? 