Find Merged Cells

[Henry G.]

How can I find merged cell?

Thanks . . .
Henry G.

 

[mathrocks]

Excellent tips for this issue...much appreciated. I also noted that if your cells also have wrapped text and are merged, you will have to check both boxes in order to find your merged cells.

 

[Herb123987]

To find out if there are ANY merged cells in the current worksheet select the entire worksheet w/ the "button" above row 1 and to the left of column A - that should make the MERGE AND CENTER button highlight itself.

To find out what columns have merged cells highlight a column by clicking on the column - that will highlight the whole column and that should make the MERGE AND CENTER button highlight itself. Possibly faster is highlight a column w/ the mouse then use shift right arrow which will highlight more columns and just keep going till the MERGE AND CENTER button highlightS itself.

When you find the column that has a merge and center then go to the top of that column and do shift-page-down until the MERGE AND CENTER button highlightS itself. Click the screen (so it doesn't jump away from where you currently are viewing) and then up arrow to see when the MERGE AND CENTER button highlightS itself.

Take care of that merged cell then just click the column header to see if there are any more that need to be taken care of if so continue from that point forward with the shift page down.

Not the best way to do it but it's the quick and dirty way.

Met my needs. Thought I'd share.

HTH

Herb

 

[hroush]

Another option to quickly find merged cells in excel 2007: find a merged cell (or make one), ctrl - f, format, choose format from cell, click the merged cell, find next. Of course, this only works if the worksheet's formatting (or, at least, the merged cells') is all the same, but I think some will still find this useful. Use in conjunction to the previous post.

 

[Vroy83]

This is simply awesome! Thank you for sharing such a cool and very useful tip.

Best,
Roy

 

[BashOrgRu]

Thanks a lot, guys, your tips are awesome indeed.

From Russia

 

[SciencePirate17]

Hi Derek,
I know this is a long shot but this code has been really helpful to me it does exactly what I had been looking for and I've been trying to expand on it a bit to do a couple extra things and I keep running into weird little errors. This code and my extras work on one sheet but if I expand it to others it that's when I start having issues.
1. If I leave my If, Then, Else statement out and it will work across multiple sheets no problem but some sheets I work with wont have merged cells
2. if it is included it seems that my else statement doesnt happen or the code is now not seeing merged cells
3. If I F8 through the cells it repeats exactly where it should but for some reason doesnt see the merged cells
and now there is a dent in my wall the size of my head because I cant figure it out haha
here is the code any and all help is appreciated

Option Explicit
Sub FindMerged()


'Modified from Mr.Excel user Derek
'Finds or doesnt find merged cells, then unmerges
'Future after unmerge calls the macros then remerges
'Future otherwise it just calls the macros


Dim str As String
Dim stri As String
Dim rng1 As Range
Dim rng2 As Range
Dim rng3 As Range
Dim sh As Worksheet


Application.ScreenUpdating = False
'For Each sh In ActiveWorkbook.Worksheets
' sh.Activate


Set rng1 = Cells.Find("*", [A1], xlFormulas, , xlByRows, xlPrevious)
Set rng2 = Cells.Find("*", [G1], xlFormulas, , xlByColumns, xlPrevious)
If Not rng1 Is Nothing Then

Set rng3 = Range([F2], Cells(rng1.row, rng2.Column))
Application.Goto rng3
End If


For Each rng3 In Selection
If rng3.MergeCells = False Then
'rng3.Select
MsgBox "no merged"
'Exit Sub
Else


str = rng3.Address
stri = stri & "," & str


End If
Next
stri = "(" & Mid(stri, 2, 1000) & ")"


'Range(stri).Select
For Each sh In ActiveWorkbook.Worksheets
sh.Activate

With Range(stri)
.UnMerge
End With


'With Range(stri)
'.Merge
'End With
Next sh


End Sub

 

[SciencePirate17]

As a quick update if there is nothing under

If rng3.MergeCells = False Then

the code will find and unmerge the cells but as above if there are no merged cells I get a "Run-time error '1004': Method 'Range' of object'_Global' Failed" at

With Range(stri)

which is why i need to have a check for unmerged cells first so it knows to skip that sheet
As above any help would be greatly appreciated
Thank you