Hi all,
When I try to execute the following line of code...
I get an runtime error message "Cannot change part of a merged cell"Sheets("Jnl").Range("B3,I3,C41,H41,C7:H38").ClearContents
Anyone know how to get round this??
Thanks
Hi all,
When I try to execute the following line of code...
I get an runtime error message "Cannot change part of a merged cell"Sheets("Jnl").Range("B3,I3,C41,H41,C7:H38").ClearContents
Anyone know how to get round this??
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
However if you want it to remain merged after clearing it, you could useSheets("Jnl").Range("B3,I3,C41,H41,C7:H38").MergeCells = False Sheets("Jnl").Range("B3,I3,C41,H41,C7:H38").ClearContents
MattDim 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
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.
Regards, Zack Barresse
Check out the KB! :|: BOARD TAGS: WHAT ARE THEY AND HOW DO I USE THEM
What is a Microsoft MVP? | Free Microsoft Courses | My Book on Excel Tables
Thinking a little more about it, you could always just do
Should accomplish what you needSheets("Jnl").Range("B3,I3,C41,H41,C7:H38") = ""
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.
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".Originally Posted by 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!!