Find Merged Cells

TryingToLearn

Well-known Member

Joined

Sep 10, 2003

Messages

733

• Feb 5, 2004

• #2

Code:

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

HTH

 

Upvote 0

D

Derek

Well-known Member

Joined

Feb 16, 2002

Messages

1,595

• Feb 5, 2004

• #3

Hi there

Modifying the advice already given, this selects merged cells in any area you select.


Sub findmerged()
Dim str As String
Dim stri As String

For Each c In Selection
If c.MergeCells Then
str = c.Address
stri = stri & "," & str
End If
Next
stri = "(" & Mid(stri, 2, 1000) & ")"
Range(stri).Select
End Sub

regards
Derek

 

Upvote 0

H

Henry G.

New Member

Joined

Feb 18, 2002

Messages

30

• Feb 5, 2004

• #4

This works very well . . . Thank You
Do you know how to find merged cells via the menu and not with a macro?

Thanks Again
Henry G.

 

Upvote 0

D

Derek

Well-known Member

Joined

Feb 16, 2002

Messages

1,595

• Feb 5, 2004

• #5

sorry, no.

Derek

 

Upvote 0

S

saruman576

New Member

Joined

Feb 16, 2003

Messages

7

• Jun 9, 2006

• #6

To find a merged cell in Excel 2003.

1. Open a Find and Replace Menu.
2. Next to the Find What Field is a Format Box, Click it.
3. This opens the Find Format Menu screen which looks very similar to the Cell Format Tabs and screens
4. On the Alignment Tab, remove any ticks in the Wrap Text or Shrink to Fit boxes.
5. Leave a tick in the Merge Cells Box and click the OK Button to return to the Find and Replace screen.
6. Now click the Find All Button and ALL merged cells are shown in the lower window and are selectable to jump to their location.

 

Upvote 0

H

Henry G.

New Member

Joined

Feb 18, 2002

Messages

30

• Jun 9, 2006

• #7

I was surprised by your response after all this time; however, I am thankful for your reply. I have asked a lot of “so called” gurus and none had the answer.

I am curious as to how you acquired this particular skill.

Thanks . . .
Henry G.

 

Upvote 0

S

saruman576

New Member

Joined

Feb 16, 2003

Messages

7

• Jun 9, 2006

• #8

I am very good at using Excel, and I had a request at work today to find a merged cell in a big spreadsheet and found this method elsewhere on the web, I cant remember where.

All the gurus seem to like immediately going for some code to solve every problem when sometimes the software already has a method of doing the task.

Glad you found it useful !!!

 

Upvote 0

S

saruman576

New Member

Joined

Feb 16, 2003

Messages

7

• Jun 9, 2006

• #9

Found the answer here

http://office.microsoft.com/en-us/assistance/HA012004951033.aspx

Microsoft video

 

Upvote 0

B

bhushanlspk

New Member

Joined

Apr 27, 2010

Messages

12

• Sep 9, 2010

• #10

saruman576

Good work dear also helped me

Thanks

 

Upvote 0