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

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

Charles

RB Smissaert wrote:
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

Yes, 100% sure.

RBS

"Die_Another_Day" wrote in message
ups.com...
Works fine on my PC. Are you sure the active sheet has merged cells?

Charles

RB Smissaert wrote:
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 RBS

I am using Excel 2003 UK and I get the same error as you.

--
Best regards
Joergen Bondesen


"RB Smissaert" wrote in message
...
Yes, 100% sure.

RBS

"Die_Another_Day" wrote in message
ups.com...
Works fine on my PC. Are you sure the active sheet has merged cells?

Charles

RB Smissaert wrote:
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

"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

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


"Peter T" <peter_t@discussions wrote in message
...
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

Thanks for confirming it is not just me.

RBS

"Joergen Bondesen" wrote in message
...
Hi RBS

I am using Excel 2003 UK and I get the same error as you.

--
Best regards
Joergen Bondesen


"RB Smissaert" wrote in message
...
Yes, 100% sure.

RBS

"Die_Another_Day" wrote in message
ups.com...
Works fine on my PC. Are you sure the active sheet has merged cells?

Charles

RB Smissaert wrote:
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 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

"RB Smissaert" wrote in message
...
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


"Peter T" <peter_t@discussions wrote in message
...
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

Hi Peter,

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

RBS

"Peter T" <peter_t@discussions wrote in message
...
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

"RB Smissaert" wrote in message
...
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


"Peter T" <peter_t@discussions wrote in message
...
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

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.

RB Smissaert wrote:

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

--

Dave Peterson

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

"Dave Peterson" wrote in message
...
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.

RB Smissaert wrote:

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

--

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


RB Smissaert wrote:

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

"Dave Peterson" wrote in message
...
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.

RB Smissaert wrote:

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

--

Dave Peterson

--

Dave Peterson

RBS,

If IsNull (RangeObject.MergeCells) or RangeObject.MergeCells then
MsgBox "Unmerge cells and try again. "
Exit Sub
End if
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware


"RB Smissaert"

wrote in 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,

Thanks to both, very simple and neat indeed.

RBS

"Jim Cone" wrote in message
...
RBS,

If IsNull (RangeObject.MergeCells) or RangeObject.MergeCells then
MsgBox "Unmerge cells and try again. "
Exit Sub
End if
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware


"RB Smissaert"

wrote in 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,