Clear Contents won't work on merged cells

[ArthurJ]

I have merged cells C1 and D1, and named it 'mergedCells'.
But I get a "400" error from the following VBA code:

[mergedCells].ClearContents

That seems odd. Is there a workaround?

Art

 

[JNW]

You have to reference all of the cells that are merged. In your example the
following should work...

Range("C11").clearcontents

It's not enough to reference the first of the merged cells (which is
bothersome because in other instances, like copy, you can reference the first
of the merged cells only)

 

[Tom Ogilvy]

This demo from the immediate window worked for me:

Range("C11").Merge
Range("C1").MergeArea.Name = "MergedCells"
rANGE("c1").Value = "Merged Cells"
? range("C1").Value
Merged Cells
? Range("MergedCells").Address
$C$1:$D$1
Range("MergedCells").ClearContents

IF it is a 400 error in a dialog with a circled red X or something like that
and no message, I believe that is caused by a bug in Excel, not trying to
clearcontents. Try closing and reopening excel and the workbook.

Tested in xl2002