| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Merged Cells quirky behavior
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Merged Cells quirky behavior
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Merged Cells quirky behavior
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" wrote in message ... 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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Merged Cells quirky behavior
Hi Art,
Try: Range("rngTest").MergeArea.ClearContents --- Regards, Norman "ArthurJ" wrote in message ... 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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Merged Cells quirky behavior
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" wrote in message ... 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" wrote in message ... 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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Dave, Norman: Got it! Thanks. (eom)
|
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Merged Cells quirky behavior
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" wrote in message ... 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Copy paste non merged to merged cells | Excel Worksheet Functions | |||
| How can I sort an Excel Doc containing merged & non-merged cells? | Excel Discussion (Misc queries) | |||
| Autofit Merged cell Code is changing the format of my merged cells | Excel Discussion (Misc queries) | |||
| how do i link merged cells to a merged cell in another worksheet. | Excel Worksheet Functions | |||
| Sorting merged cellsHow do I sort merged cells not identically siz | Excel Worksheet Functions | |||
Linear Mode
