Identifying merged cells (but not one at a time)

Dr. Demento

Well-known Member
Joined
Nov 2, 2010
Messages
573
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"

Code:
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.
 

Some videos you may like

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

starl

Administrator
Joined
Aug 16, 2002
Messages
5,969
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
  2. MacOS
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

Code:
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

Board Regular
Joined
Mar 8, 2016
Messages
216
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

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,675
Office Version
  1. 2010
Platform
  1. Windows
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...
Code:
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

Well-known Member
Joined
Nov 2, 2010
Messages
573

ADVERTISEMENT

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

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,675
Office Version
  1. 2010
Platform
  1. Windows
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

Well-known Member
Joined
Nov 2, 2010
Messages
573

ADVERTISEMENT

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

Administrator
Joined
Aug 16, 2002
Messages
5,969
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
  2. MacOS
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

Well-known Member
Joined
Nov 2, 2010
Messages
573
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

Administrator
Joined
Aug 16, 2002
Messages
5,969
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
  2. MacOS
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,371
Messages
5,595,784
Members
414,020
Latest member
Meghdad

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top