Hi,

I have a range of cells that I need to clear before doing other stuff with
them. These cells are formatted in a particular way and some of them are
merged. I am trying to use:

Sheets("mobilisation").Range("h2:n8").Clear

but this is returning an error saying that Excel "cannnot change part of a
merged cell"

As I can both Clear and Delete the content of these cells manually, I assume
that I haven't got the syntax correct to do it in VBA?

Any help would be appreciated.

TIA

Dave

Try

Sheets("mobilisation").Range("h2:n8").MergeArea.Clear

HTH,
Barb Reinhardt

"Risky Dave" wrote:

> Hi,
>
> I have a range of cells that I need to clear before doing other stuff with
> them. These cells are formatted in a particular way and some of them are
> merged. I am trying to use:
>
> Sheets("mobilisation").Range("h2:n8").Clear
>
> but this is returning an error saying that Excel "cannnot change part of a
> merged cell"
>
> As I can both Clear and Delete the content of these cells manually, I assume
> that I haven't got the syntax correct to do it in VBA?
>
> Any help would be appreciated.
>
> TIA
>
> Dave

Barb,

thanks for the reply. this now throws up:

Run-time error 1004:

Application-defined or object-defined error


As you have probably guessed, I'm no programmer so I don't really understand
what this is trying to tell me.

Dave

"Barb Reinhardt" wrote:

> Try
>
> Sheets("mobilisation").Range("h2:n8").MergeArea.Clear
>
> HTH,
> Barb Reinhardt
>
> "Risky Dave" wrote:
>
> > Hi,
> >
> > I have a range of cells that I need to clear before doing other stuff with
> > them. These cells are formatted in a particular way and some of them are
> > merged. I am trying to use:
> >
> > Sheets("mobilisation").Range("h2:n8").Clear
> >
> > but this is returning an error saying that Excel "cannnot change part of a
> > merged cell"
> >
> > As I can both Clear and Delete the content of these cells manually, I assume
> > that I haven't got the syntax correct to do it in VBA?
> >
> > Any help would be appreciated.
> >
> > TIA
> >
> > Dave

Try this

Sheets("mobilisation").Range("h2:n8").MergeArea.Unmerge


"Risky Dave" <(E-Mail Removed)> wrote in message
news:E2253815-8707-40F3-B394-(E-Mail Removed)...
> Barb,
>
> thanks for the reply. this now throws up:
>
> Run-time error 1004:
>
> Application-defined or object-defined error
>
>
> As you have probably guessed, I'm no programmer so I don't really
> understand
> what this is trying to tell me.
>
> Dave
>
> "Barb Reinhardt" wrote:
>
>> Try
>>
>> Sheets("mobilisation").Range("h2:n8").MergeArea.Clear
>>
>> HTH,
>> Barb Reinhardt
>>
>> "Risky Dave" wrote:
>>
>> > Hi,
>> >
>> > I have a range of cells that I need to clear before doing other stuff
>> > with
>> > them. These cells are formatted in a particular way and some of them
>> > are
>> > merged. I am trying to use:
>> >
>> > Sheets("mobilisation").Range("h2:n8").Clear
>> >
>> > but this is returning an error saying that Excel "cannnot change part
>> > of a
>> > merged cell"
>> >
>> > As I can both Clear and Delete the content of these cells manually, I
>> > assume
>> > that I haven't got the syntax correct to do it in VBA?
>> >
>> > Any help would be appreciated.
>> >
>> > TIA
>> >
>> > Dave

I should have included that after the cells are unmerged, you can then clear
them. If you require merged cells in that range, then you will have to
merge them again.

Excel will not let you alter merged cells because it does not know which
part of the cell you really want to change. You have to unmerge them, do
your thing and then merge them again.



"Risky Dave" <(E-Mail Removed)> wrote in message
news:E2253815-8707-40F3-B394-(E-Mail Removed)...
> Barb,
>
> thanks for the reply. this now throws up:
>
> Run-time error 1004:
>
> Application-defined or object-defined error
>
>
> As you have probably guessed, I'm no programmer so I don't really
> understand
> what this is trying to tell me.
>
> Dave
>
> "Barb Reinhardt" wrote:
>
>> Try
>>
>> Sheets("mobilisation").Range("h2:n8").MergeArea.Clear
>>
>> HTH,
>> Barb Reinhardt
>>
>> "Risky Dave" wrote:
>>
>> > Hi,
>> >
>> > I have a range of cells that I need to clear before doing other stuff
>> > with
>> > them. These cells are formatted in a particular way and some of them
>> > are
>> > merged. I am trying to use:
>> >
>> > Sheets("mobilisation").Range("h2:n8").Clear
>> >
>> > but this is returning an error saying that Excel "cannnot change part
>> > of a
>> > merged cell"
>> >
>> > As I can both Clear and Delete the content of these cells manually, I
>> > assume
>> > that I haven't got the syntax correct to do it in VBA?
>> >
>> > Any help would be appreciated.
>> >
>> > TIA
>> >
>> > Dave