VB - count merged cells

[velohead]

Hi All,

I need to count the number of occurrences of merged cells within an active worksheet.
Eg if 1 merged cell exists over what was previously 4 cells, then I just want to count the 1 occurrence.
I presume this is possible with VB ?

Thanks

 

[Michael M]

there's a number of different options to find merged cells here.....but, I'd do everything possible to avoid merged cells...they will break your heart AND your workbook !!!


Identifying Merged Cells (Microsoft Excel)

 

[velohead]

I’ll read the link just now.

I personally don’t like or use merged cells.
One can achieve similar results with “centre across selection”

However, my learned colleagues in other depts., such as in Sales, and in Marketing just love merged cells.
And I have to deal with their spreadsheets. D'oh!

 

[Michael M]

You could also use a macro to remove them....

Sub UnmergeAllMergedCells()
    Dim WS As Worksheet
    For Each WS In Worksheets
        WS.Cells.UnMerge
    Next
End Sub

And maybe you should explain to them the consequences of using the B####y things

There are rare occasions when merging cells are appropriate, and a data table is not one of them.
It’s quite common when you export a report to Excel from another system that there are several merged cells at the top or bottom of the spreadsheet.
Often when I get one of these, or inherit someone else’s spreadsheet I check for and remove all merged cells (check out number 2 on my top 5 keyboard shortcuts).
Why shouldn’t you merge cells?
It plays havoc with sorting and filtering.
You can’t drag down formulas through cells that are merged / unmerged differently.
It affects keyboard shortcuts to select an entire column/row.
They can’t be used in Table functionality introduced in Excel 2007.
They’re a pain when dealing with macros.
There are other reasons.

 

[Rick Rothstein]

Well, if you are truly stuck with those merged cells, here is a function that will tell you how many of them are on the active worksheet...

Function MergeCount() As Long
  Dim C As Range, FirstAddress As String
  Application.FindFormat.MergeCells = True
  Set C = ActiveSheet.UsedRange.Find("", searchformat:=True)
  If Not C Is Nothing Then
    FirstAddress = C.Address
    Do
      MergeCount = MergeCount + 1
      Set C = ActiveSheet.UsedRange.Find("", C, searchformat:=True)
    Loop While Not C Is Nothing And FirstAddress <> C.Address
  End If
End Function

 

[Rick Rothstein]

Well, if you are truly stuck with those merged cells, here is a function that will tell you how many of them are on the active worksheet...

Function MergeCount() As Long
  Dim C As Range, FirstAddress As String
  [B][COLOR="#FF0000"]Application.FindFormat.Clear[/COLOR][/B]
  Application.FindFormat.MergeCells = True
  Set C = ActiveSheet.UsedRange.Find("", searchformat:=True)
  If Not C Is Nothing Then
    FirstAddress = C.Address
    Do
      MergeCount = MergeCount + 1
      Set C = ActiveSheet.UsedRange.Find("", C, searchformat:=True)
    Loop While Not C Is Nothing And FirstAddress <> C.Address
  End If
  [B][COLOR="#FF0000"]Application.FindFormat.Clear[/COLOR][/B]
End Function

I forgot to include the two lines of code shown in red above. Including the first one makes sure you don't have a problem with a previously used search format and the second one is just good housekeeping to make sure the next time the Find function (either VB's Find function or Excel's Find dialog box) is used, the user does not accidentally inherit the search format used in this function.