# Identifying merged cells (but not one at a time)

#### Dr. Demento

##### Well-known Member
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.

### Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

#### 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

Code:
``````If c.MergeCells And c.Address = c.MergeArea.Cells(1, 1).Address Then
End If``````

obviously change the debug.print to what you want

#### Meggesto

##### Board Regular
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 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
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
Next
End Sub``````

#### Dr. Demento

##### Well-known Member

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

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

##### Well-known Member
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

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

### Forum statistics

1,167,943
Messages
5,856,438
Members
431,814
Latest member
qualitypavingstone

### 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.

### Which adblocker are you using?

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

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