Clearing Contents of Merged Cells

[BexleyManor]

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??

Thanks

[mvidas]

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]

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]

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]

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.

[mvidas]

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]

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]

{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]

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!!