Hi Bart,

I don't have FindFormat in my xl version so can't replicate, but see if you
can adapt this

Set r = Cells.Find(What:="1", After:=ActiveCell.MergeArea(1), _
LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)
r.Activate
Debug.Print r.Address, r.MergeArea.Address

Regards,
Peter T

"RB Smissaert" wrote in message
...
Having a problem finding merged cells through VBA.

Doing it through the interface works fine and I can also record the macro:

Sub Macro1()

With Application.FindFormat
.WrapText = False
.ShrinkToFit = False
.MergeCells = True
End With

Cells.Find(What:="", After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=True).Activate

End Sub

However if I then run the above macro it fails: Object variable or With
block not set,
which I think it can't find the merged cells.

Even when I make sure that any other formatting options are cleared I get
the same error:

Sub FindMergedCells()

Application.FindFormat.Clear
Application.FindFormat.MergeCells = True

Cells.Find(What:="", _
After:=ActiveCell, _
LookIn:=xlValues, _
LookAt:=xlPart, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=True).Activate

Application.FindFormat.Clear

End Sub

I know I could loop through all the cells of the used range and look for
MergeCells, but that is
much slower.

Any suggestions how to do this?


RBS