Clearing Contents of Merged Cells

Hi all,

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

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

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

Code:

---

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

Code:

---

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

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

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!

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

Thinking a little more about it, you could always just do

Code:

---

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

---

Should accomplish what you need :)
Matt

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.

Quote:

---

Originally Posted by BexleyManor

{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".

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