Merged Cells quirky behavior

[ArthurJ]

I have merged cells A1..C1 and named it rngTest.
I enter data into rngTest.

Range("rngTest").ClearContents
'Generates Error 1004 Cannot change part of a merged cell.

Range("rngTest").Select 'or Activate
Selection.ClearContents
'Runs successfully.

This sure seems bizarre. Does your machine perform like mine? Any suggested
workarounds? I would prefer NOT to select these cells programmatically.

Art

[Dave Peterson]

One way:

Range("rngTest").value = ""

or

Range("rngTest").mergearea.clearcontents



ArthurJ wrote:
>
> I have merged cells A1..C1 and named it rngTest.
> I enter data into rngTest.
>
> Range("rngTest").ClearContents
> 'Generates Error 1004 Cannot change part of a merged cell.
>
> Range("rngTest").Select 'or Activate
> Selection.ClearContents
> 'Runs successfully.
>
> This sure seems bizarre. Does your machine perform like mine? Any suggested
> workarounds? I would prefer NOT to select these cells programmatically.
>
> Art

--

Dave Peterson

[Norman Jones]

Hi Art,

Try:

Range("rngTest").MergeArea.ClearContents


---
Regards,
Norman


"ArthurJ" <[email protected]> wrote in message
news:[email protected]...
>I have merged cells A1..C1 and named it rngTest.
> I enter data into rngTest.
>
> Range("rngTest").ClearContents
> 'Generates Error 1004 Cannot change part of a merged cell.
>
> Range("rngTest").Select 'or Activate
> Selection.ClearContents
> 'Runs successfully.
>
> This sure seems bizarre. Does your machine perform like mine? Any
> suggested
> workarounds? I would prefer NOT to select these cells programmatically.
>
> Art

[Sasa Stankovic]

it is always better to use ".Select".... this way you are sure Excel knows
what to select and later than what to do with selection!
much safer and it doesn't slows down your macro if you are looking for
optimization....
"ArthurJ" <[email protected]> wrote in message
news:[email protected]...
>I have merged cells A1..C1 and named it rngTest.
> I enter data into rngTest.
>
> Range("rngTest").ClearContents
> 'Generates Error 1004 Cannot change part of a merged cell.
>
> Range("rngTest").Select 'or Activate
> Selection.ClearContents
> 'Runs successfully.
>
> This sure seems bizarre. Does your machine perform like mine? Any
> suggested
> workarounds? I would prefer NOT to select these cells programmatically.
>
> Art

[Norman Jones]

Hi Sasa,

> it is always better to use ".Select

Contrawise, I would suggest that selections are rarely necessary or
desirable and are usually inefficient.


---
Regards,
Norman



"Sasa Stankovic" <[email protected]> wrote in message
news:[email protected]...
> it is always better to use ".Select".... this way you are sure Excel knows
> what to select and later than what to do with selection!
> much safer and it doesn't slows down your macro if you are looking for
> optimization....
> "ArthurJ" <[email protected]> wrote in message
> news:[email protected]...
>>I have merged cells A1..C1 and named it rngTest.
>> I enter data into rngTest.
>>
>> Range("rngTest").ClearContents
>> 'Generates Error 1004 Cannot change part of a merged cell.
>>
>> Range("rngTest").Select 'or Activate
>> Selection.ClearContents
>> 'Runs successfully.
>>
>> This sure seems bizarre. Does your machine perform like mine? Any
>> suggested
>> workarounds? I would prefer NOT to select these cells programmatically.
>>
>> Art
>
>

[ArthurJ]

[Peter T]

You already have good answers from Dave & Norman, so just this-

> I have merged cells A1..C1 and named it rngTest.

I expect you only named A1. Had you named A1:C1 your code would work without
modification.

If the cells are already merged you'd need to enter the address fully in the
RefersTo box in the names dialog Ctrl-F3.

Regards,
Peter T
-
"ArthurJ" <[email protected]> wrote in message
news:[email protected]...
> I have merged cells A1..C1 and named it rngTest.
> I enter data into rngTest.
>
> Range("rngTest").ClearContents
> 'Generates Error 1004 Cannot change part of a merged cell.
>
> Range("rngTest").Select 'or Activate
> Selection.ClearContents
> 'Runs successfully.
>
> This sure seems bizarre. Does your machine perform like mine? Any
suggested
> workarounds? I would prefer NOT to select these cells programmatically.
>
> Art