Problem finding merged cells

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
 
D

Die_Another_Day

Works fine on my PC. Are you sure the active sheet has merged cells?

Charles
 
P

Peter T

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
 
R

RB Smissaert

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
 
P

Peter T

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
 
R

RB Smissaert

Hi Peter,

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

RBS
 
D

Dave Peterson

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.
 
R

RB Smissaert

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
 
D

Dave Peterson

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

conso macro 0
Find sometimes ignores Application.FindFormat 3
Find method skips over cells 1
Find Question 1
If Cells.Find returns run-time error '91': Object variableor With block variable not set 1
Can't get the macro to continue through a range 2
Data Cleanse Macro Running Very Slow 0
Need help with correct syntax please 2

Top