PDA

View Full Version : Solved: Clearing Contents of Merged Cells



BexleyManor
08-19-2004, 06:27 AM
Hi all,

When I try to execute the following line of code...

Sheets("Jnl").Range("B3,I3,C41,H41,C7:H38").ClearContents

I get an runtime error message "Cannot change part of a merged cell"

Anyone know how to get round this?? :dunno

Thanks

mvidas
08-19-2004, 07:25 AM
Hi BexleyManor,

One (or more) of the cells must be part of a merged cell. If you don't need it merged you could use code such as

Sheets("Jnl").Range("B3,I3,C41,H41,C7:H38").MergeCells = False
Sheets("Jnl").Range("B3,I3,C41,H41,C7:H38").ClearContents

However if you want it to remain merged after clearing it, you could use

Dim cLL As Range, mergeAddress As String
For Each cLL In Sheets("Jnl").Range("B3,I3,C41,H41,C7:H38").Cells
If cLL.MergeArea.Address <> cLL.Address Then
mergeAddress = cLL.MergeArea.Address(0, 0)
cLL.MergeCells = False
cLL.ClearContents
Range(mergeAddress).MergeCells = True
Else
cLL.ClearContents
End If
Next

Matt

BexleyManor
08-19-2004, 07:37 AM
Hi Matt,

Many thanks for the prompt input, much appreciated. I tried your second suggestion which worked. However, I notice it takes a while for the code to run which my users would immediately panic and set to their machine with a hammer, I kid you not!!

Is there a quicker way of performing this action or will I need to confiscate all hammers on site??!!

Many Thanks

BexleyManor
08-19-2004, 07:48 AM
Actually, scrap that last comment. (Unless somebody comes up with something else!!)

I used the application.screenupdating and that should run it smooth enough for even my most lairy of users!!!

Thanks once again Matt, you the man!

Zack Barresse
08-19-2004, 08:17 AM
If you wanted to get really fancy about it, you could make a userform that says something like 'Processing...' and display it before everything starts, then take it down when everythings done. :dunno

mvidas
08-19-2004, 08:55 AM
Thinking a little more about it, you could always just do

Sheets("Jnl").Range("B3,I3,C41,H41,C7:H38") = ""

Should accomplish what you need :)
Matt

BexleyManor
08-20-2004, 01:53 AM
Thanks for the further input Matt. Being a 'quick fix' merchant, your last suggestion suits me down to the ground!!

Zack, thanks for the suggestion mate but If my users saw anything 'pop up' on their screen they'd probably level the office and run for the hills because the 'magic box had spoken tounges to them', I kid you not!!!

Thanks fellas for the valued input.

Jacob Hilderbrand
08-20-2004, 02:11 AM
{Snip} If my users saw anything 'pop up' on their screen they'd probably level the office and run for the hills because the 'magic box had spoken tounges to them', I kid you not!!!{Snip}
Your users must have trained mine. Believe it or not, I spent 10 minutes on the phone with one employee trying to get her to press the escape key. You would think that saying "press the key labeled Esc on the top-left of the keyboard would have worked".

BexleyManor
08-20-2004, 02:32 AM
No Jacob, not trained, more likely they all escaped from the same mental institution, and in my case, they became accountants sent on a mission to make me bald and give me a nervous twitch!!