I have named 2 merged cells in a spreadsheet, say A1 & B1. The merged cell
is named "range1". I am trying to clear the contents from VBA code, using:
Range("range1").ClearContents
I get an error that says "Cannot change part of a merged cell"
Is there a simple workaround?
Assume I only know the range name and not the component cells.

Thanks

try:

Range("range1").value = ""
or
Range("range1").mergearea.ClearContents

AVR wrote:
>
> I have named 2 merged cells in a spreadsheet, say A1 & B1. The merged cell
> is named "range1". I am trying to clear the contents from VBA code, using:
> Range("range1").ClearContents
> I get an error that says "Cannot change part of a merged cell"
> Is there a simple workaround?
> Assume I only know the range name and not the component cells.
>
> Thanks

--

Dave Peterson

..mergearea worked perfectly. Thanks

"Dave Peterson" wrote:

> try:
>
> Range("range1").value = ""
> or
> Range("range1").mergearea.ClearContents
>
> AVR wrote:
> >
> > I have named 2 merged cells in a spreadsheet, say A1 & B1. The merged cell
> > is named "range1". I am trying to clear the contents from VBA code, using:
> > Range("range1").ClearContents
> > I get an error that says "Cannot change part of a merged cell"
> > Is there a simple workaround?
> > Assume I only know the range name and not the component cells.
> >
> > Thanks
>
> --
>
> Dave Peterson
>

Hi AVR

The following is just modified code from excel help

Option Explicit
Dim Ma As Range

Private Sub CommandButton1_Click()

Set Ma = Range("Range1").MergeArea

If Range("Range1").MergeCells Then

Ma.ClearContents

End If

End Sub

Hope it helps

S