Searching (Find) in Merged Cells

W

William Bartusek

How can you search in merged cells for text content? The
Find Method works on single cells but I can't get it to
work on merged cells.
 
B

Bernie Deitrick

William,

Excel treats merged cells as if they were the cell at the upper left of the
merged area. In VBA:

Sub SimpleFind()
Dim myCell As Range
Set myCell = Cells.Find("find this", , xlValues, xlPart)
If Not myCell Is Nothing Then
MsgBox "Found in " & myCell.Address
Else
MsgBox "Not Found"
End If
End Sub

In Excel, simply select all the cells using the select all button to the
upper left of A1, then use Ctrl F.

HTH,
Bernie
MS Excel MVP
 
D

Dave Peterson

Different versions of excel treat merged cells, er, differently.

Put "this" in d7, e5, f7
merge E5:E9

Ctrl-F
and Find this

Hit repeat Find a few times and watch the pattern of found cells.

If you're using xl2002 (maybe xl2003), hit Find All. But be prepared to hit
escape to stop it!

Works that way in code, too.

IIRC, xl97 worked ok. And xl2k introduced this "feature"!
 
Top