Identifying merged cells (but not one at a time)

[Dr. Demento]

How could I adapt the code below to only give the range of a block the merged cells once and then move on to the next merged block?

For example: if Range("A1:A4") and Range ("C5:C10") are merged, I'd like the output to be
"A1:A4 is merged"
"C5:C10 is merged"

NOT
"A1 is merged"
"A2 is merged"
. . . (8 more times) . . .
"C10 is merged"

Sub FindMerged1()
    Dim c As Range
    For Each c In ActiveSheet.UsedRange
        If c.MergeCells Then
            MsgBox c.Address & " is merged"
        End If
    Next
End Sub

Source: Allen Wyatt

Somehow, I'm wondering if cycling thru each .MergeArea or .MergeCells is a solution, but I don't know how to implement it.

Also, I'm not interested in UnMerging the block, just identifying the location.

Any thoughts? Thanks, y'all.

 

[starl]

Nice Puzzle!
I don't know of how to pic JUST the merged cells, but you can use this logic to get the result you requested

If c.MergeCells And c.Address = c.MergeArea.Cells(1, 1).Address Then
        Debug.Print c.MergeArea.Address
End If

obviously change the debug.print to what you want

 

[Meggesto]

Sub FindAllMerged()

Dim c As Range
Dim sMsg As String
sMsg = ""
For Each c In ActiveSheet.UsedRange
If c.MergeCells Then
If sMsg = "" Then
sMsg = "Merged worksheet cells:" & vbCr
End If
sMsg = sMsg & Replace(c.MergeArea.Address, "$", "") & vbCr
End If
Next
If sMsg = "" Then
sMsg = "No Merged Cells Found."
End If
Msgbox sMsg

This isn't my code. I found it and made one change from C.Address to C.MergeArea.Address.
This works the same as your code but instead of prompting each time it hits a merged cell it will just start a running list and display after there are no more merged cells. However this still does repeat itself but at least you won't have to click through the prompts and it will tell you which cells are merged as one cell.

 

[Rick Rothstein]

Here is a macro that will bundle all of the merged areas into a single range variable (named MCells) and then iterate them one-at-a-time to show you their address (which is what your posted code is ultimately doing) even though what you may be planning to do with these merged areas may not, in fact, require being iterated one-at-a-time...

Sub FindMCells()
  Dim Cell As Range, Ar As Range, MCells As Range, CurrentMerge As Range
  For Each Cell In ActiveSheet.UsedRange
    If Cell.MergeCells Then
      If MCells Is Nothing Then
        Set MCells = Cell.MergeArea
        Set CurrentMerge = Cell.MergeArea
      ElseIf Intersect(Cell, CurrentMerge) Is Nothing Then
        Set MCells = Union(MCells, Cell.MergeArea)
      End If
    End If
  Next
[COLOR="#008000"]  ' At this point in the code, the MCells range contains references
  ' to each merged area, so let's examine them one-at-a-time[/COLOR]
  For Each Ar In MCells.Areas
    MsgBox Ar.Address(0, 0)
  Next
End Sub

 

[Dr. Demento]

Thanks everyone! That's some powerful code.

Starl - as a fan of tight code, I like yours alot. Works perfectly. In an effort to make it tighter, I tried to remove the .address reference for the second logic check; this worked fine for normal merged blocks, but if there was a merged block in an otherwise empty row/column, it would duplicate the listing of the range (number of duplicates = number of cells in merged block). However, when I put the .address back in, it worked in all cases. What would cause the different behavior??

Meggesto - thank you for the code; I had also found this. However, it still lists out each cell of all merged blocks.

Rick - thank you for this stand-alone. I always like adding new code blocks to my repetoire. I would have never thought to make a conglomerated range and then re-analyze. Thank you for a new way of thinking.

 

[Rick Rothstein]

Rick - thank you for this stand-alone. I always like adding new code blocks to my repetoire. I would have never thought to make a conglomerated range and then re-analyze. Thank you for a new way of thinking.

The only reason for the "re-analyze" loop was because the code you posted did that. The point I was trying to get across in my lead-in description is that you might not have to do the "re-analyze" loop, rather, depending on what you were attempting to do, you might be able to replace the loop with a single line of code. For example, if you wanted to color all of the merged cells yellow, you could do this instead of looping the merged areas individually...

MCells.Interior.Color = vbYellow

Or if you wanted to clear the contents of all the merged cells, again, a single line of code is all that is needed...

MCells.ClearContents

Or if you wanted to put the same text (let's say N/A) in all the merged areas...

MCells.Value = "N/A"

That was the reason behind making a "conglomerated range", so that you code do the same thing to each of them with just one line of code (assuming you that is what you were ultimately after) rather than looping the merged areas individually (although you could do that if you needed to do different things to the individual merged areas as well).

 

[Dr. Demento]

I understand; thank you for your additional explanation.

My over-arching purpose is to perform data validation on any worksheet, to include structural issues (merged cells, blank rows/columns within the UsedRange, etc) and log the location of the offending cells. The user can choose two options - eliminate the issues (unmerge, delete row/col) or to leave them alone but report the location only. When the user chooses to correct/eliminate the issues, the location of the merged block is logged for the first cell and then the block is unmerged; after that, .MergeCells is False for that particular block and it moves on to the next merged block. This means that each merged block only gets its address logged once.

However, if the user chooses to simply log the location of the issues without correcting them, that's where my original question comes it - otherwise, the log becomes unwieldy and redundant as each cell within a merged block is logged.

Your and starl's solutions both provided the needed output. Thanks again.

 

[starl]

not exactly sure what change you tried making or why removing a property would make it "tighter."
Good programming always tries to be specific. Leaving the desired property to chance is asking for trouble..

 

[Dr. Demento]

Yeah, it took me a couple of hours to figure out that comparing two ranges (c <> c.MergeArea.Cells(1, 1)) is not the same as comparing their addresses (c.address = c.MergeArea.Cells(1, 1).address), so I totally see your point!

I guess I've always been a fan of getting results with minimal code; guess I should save that for when I really understand what's going on

 

[starl]

The default property of a range is the value, so that's what you were comparing.
Even knowing that's the default property, though, it's good to specify, especially if
1. you forget
2. it's a good reminder
3. someone else is reviewing your code and doesn't know