Problem finding merged cells

  • Thread starter Thread starter RB Smissaert
  • Start date Start date
R

RB Smissaert

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
 
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
 
Hi Peter,

Not sure how that is meant to be working.
It produces a range even when there are no merged cells.

I have taken a different approach now.
As FormatFind is not in Excel versions before 2002 I have to drop it in any
case.
As most of my users won't be using merged cells I have made a setting in my
..ini:
Look for merged cells with a default of False.

I still wonder if there is a faster way to determine if there are merged
cells, without
using a FindFormat. Maybe with .xll via the Excel API?

RBS
 
Hi Bart,

I hadn't appreciated you were trying to find merged cells (I should have!).
However with merged cells often you need to refer to cel.Mergearea(1).

Afraid in xl2000 to find merged cells it means looping cells and if
..Mergearea.count > 1

Regards,
Peter T
 
Hi Peter,

Thanks for trying. I have a reasonable solution for this now, but will keep
on the lookout for something better.

RBS
 
If you're just trying to unmerge all the merged cells:

ActiveSheet.Cells.MergeCells = False

If you really want to find the first one and clear just that, ignore this
message.
 
All I want to do is determine if the sheet has merged cells or not.
It looks there somehow is a bug with FindFormat.

RBS
 
If you select all the cells and then look at format|Cells|alignment tab, you'll
see that merged cells can be checked (filled) or empty (unchecked) or greyed
(lightgreen??).

That greyed version is a mixture of some merged and some not merged.



Dim HasMerged As Variant 'true, false, or null

HasMerged = ActiveSheet.Cells.MergeCells

If HasMerged = True Then
MsgBox "wow--all the cells are merged into one cell"
ElseIf HasMerged = False Then
MsgBox "no merged cells"
Else 'isnull(hasmerged) will be true
MsgBox "mixture"
End If
 
Back
Top