Clear Contents Merged Cells

[melewie]

Before I start yes I have merged cells!!! This was never meant to be used with macros, but the people have spoken and I dislike them <?xml:namespace prefix = v ns = "urn:schemas-microsoft-com:vml" /><v:shapetype id=_x0000_t75 stroked="f" filled="f" path="m@4@5l@4@11@9@11@9@5xe" oreferrelative="t" o:spt="75" coordsize="21600,21600"> <v:stroke joinstyle="miter"></v:stroke><v:formulas><v:f eqn="if lineDrawn pixelLineWidth 0"></v:f><v:f eqn="sum @0 1 0"></v:f><v:f eqn="sum 0 0 @1"></v:f><v:f eqn="prod @2 1 2"></v:f><v:f eqn="prod @3 21600 pixelWidth"></v:f><v:f eqn="prod @3 21600 pixelHeight"></v:f><v:f eqn="sum @0 0 1"></v:f><v:f eqn="prod @6 1 2"></v:f><v:f eqn="prod @7 21600 pixelWidth"></v:f><v:f eqn="sum @8 21600 0"></v:f><v:f eqn="prod @7 21600 pixelHeight"></v:f><v:f eqn="sum @10 21600 0"></v:f></v:formulas><vath o:connecttype="rect" gradientshapeok="t" o:extrusionok="f"></vath><?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o:lock aspectratio="t" v:ext="edit"></o:lock></v:shapetype><v:shape id=_x0000_i1025 style="WIDTH: 12pt; HEIGHT: 12pt" alt="0" type="#_x0000_t75"><v:imagedata o:href="http://www.mrexcel.com/forum/images/smilies/biggrin.gif" src="file:///C:\Users\U522393\AppData\Local\Temp\msohtmlclip1\01\clip_image001.gif"></v:imagedata></v:shape><o></o>
<o></o>
Problem I am having is I have a named range 'Populate_RNG' which covers exactly 1 gazillion cells and contains merged cells <v:shape id=_x0000_i1026 style="WIDTH: 11.25pt; HEIGHT: 11.25pt" alt="0" type="#_x0000_t75"><v:imagedata o:href="http://www.mrexcel.com/forum/images/smilies/icon_mad.gif" src="file:///C:\Users\U522393\AppData\Local\Temp\msohtmlclip1\01\clip_image002.gif"></v:imagedata></v:shape><o></o>
<o></o>
So the code

Range("Populate_RNG").ClearContents

doesn’t work!!<o></o>
<o></o>
Is there a way around this as reformatting the sheet would be a nightmare and I want to go home soon!<o></o>
<o></o>
Any help would be great<o></o>

 

[JackDanIce]

This just worked for me:

Range("TestRange") = Null

Where TestRange is cells A1:G22 merged into one

 

[GlennUK]

Are the merged areas overlapping cells outside of that named range by any chance?

 

[melewie]

JackDanIce - Thanks allot that seemed to work, you are now my favourite person in the world!!

GlennUK - No all the merged cells are 'input' cells so they dont overlap all are contained within the range? should this normally work with merged cells?

 

[GlennUK]

It worked for me. Weird, huh.

 

[JackDanIce]

You're welcome

 

[melewie]

It worked for me. Weird, huh.

I will double check when I get chance like I said the range does contain 1 gazillion cells so to be honest I didnt really check for that....I just saw merged cells and blamed them, they normaly cause all the worlds problems

 

[GlennUK]

It's very weird though ... it crashed first time, then worked the second time ( and 3rd and 4th and for all other tests until I had merged cells overlapping the edge of the range ).

 

[melewie]

It's very weird though ... it crashed first time, then worked the second time ( and 3rd and 4th and for all other tests until I had merged cells overlapping the edge of the range ).

Strange....I going to blame merged cells they are the work of the devil! Every time you merge cells a programmer somewhere dies
I will have a play tomorrow and see what happens

 

[GlennUK]

Strange....I going to blame merged cells they are the work of the devil! Every time you merge cells a programmer somewhere dies
I will have a play tomorrow and see what happens

... oh yes indeed!!!!