I keep getting errors "cannot change part of a merged cell"
I keep getting errors "cannot change part of a merged cell"
~Anne Troy
Nope you can't do that. You could use a selection instead.
There's also other ways around it. Let me know if you need something else.cell.Select Selection.ClearContents
Edit: Btw, if you need to, post your code or an example of it. Or explain what you're trying to do with merged cells. Also, this is a big selling point for me advocating to stay away from merged cells as much as possible.
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
when I used next line on the merged cell I retrieve the same error as you retrieve:
but when using the following line it goes well:ActiveCell.ClearContents
perhaps this helps.ActiveCell = ""
Jeroen
ActiveSheet.Unprotect Password:="" Range("rng1").ClearContents ActiveSheet.Protect Password:=""
~Anne Troy
When I do the = "", I get "does not support this property or method"
~Anne Troy
I can send the workbook to anyone who wants to help.
~Anne Troy
Dreamboat,
Please send the workbook.
Brad
Sent!
~Anne Troy
Brad: I said it was the gray cells. And it is, except for the one at top-right. Leave that one alone.
~Anne Troy
Thanks, Brad!! It appears to work. I forwarded to the owner. She's gone for quite some time, but we'll probably find out tonight yet how she fares.
CANNOT thank you enough!
~Anne Troy
Oh...and for those listening in, here's Brad's code:
Sub EraseData() Dim cel As Range Application.ScreenUpdating = False 'Stops screen flicker & code runs faster ActiveSheet.Unprotect Password:="" Range("rng1").ClearContents Application.EnableEvents = False 'Don't run Worksheet_Change sub while clearing rng2, rng3, rng4, rng5 & rng6 Range("rng2").ClearContents For Each cel In Range("rng3") 'Contains merged cells, so do this to avoid error cel.MergeArea.ClearContents Next cel For Each cel In Range("rng4") cel.MergeArea.ClearContents Next cel For Each cel In Range("rng5") cel.MergeArea.ClearContents Next cel For Each cel In Range("rng6") cel.MergeArea.ClearContents Next cel Application.EnableEvents = True ActiveSheet.Protect Password:="" Application.ScreenUpdating
~Anne Troy
Hi Anne, it wont matter in this case as you are simply clearing contents, but when you loop through a range it looks at every cell individually, even if the cell is merged. So if A1:A3 were merged and A4:A5 were merged then a loop through A1:A5 is looping through 5 cells not 2.
Ideally you would check to see if the cell was merged, then loop through the merged area seprately. This is just FYI...
Sub Example() Dim Rng As Range, c As Range, cell As Range Set Rng = Range("A1:A6") For Each c In Rng 'you should put an IsError check here if you are looking at cell values If c.MergeCells Then If c.Address = c.MergeArea(1).Address Then For Each cell In c.MergeArea 'do what ever to merged cell Next cell End If Else 'do what ever to non merged cell End If Next c End Sub
Hi, parry! Thanks! Then....how do I tell it all those named ranges are my range, instead of just A1:A6?
~Anne Troy
Hi Anne, change this line...
to this to join all the cells into one big range...Set Rng = Range("A1:A6")
The code you posted will work, but it just means its clearing the merged cells several times (once for each cell in the merged area). Not a biggie on a small range but may be slightly slower for a larger range.Set Rng = Union(Range("Rng3"),Range("Rng4"),Range("Rng5"),Range("Rng6"))
Because you are just clearing contents you dont need to loop through each cell within the Merged area so heres a better example. You may still want to include the other actions such as screenupdating, turning off events etc.
Sub BetterExample() Dim Rng As Range, c As Range Set Rng = Union(Range("Rng3"), Range("Rng4"), Range("Rng5"), Range("Rng6")) For Each c In Rng If c.MergeCells Then If c.Address = c.MergeArea(1).Address Then c.MergeArea.ClearContents Else 'do what ever to non merged cell End If Next c End Sub
Well, Parry, to be honest, I always have TWO goals in a case like this.
One--the best way.
Two--the best way for this job. Since this job is just these ranges...I'll let it go, since I already sent her the file.
But it's good ('specially for me who's learning) to get the *best* way.
When this workbook is all done, we have her permission to "genericize it" and load it up as a sample...
~Anne Troy
Anne,
I checked each of those named ranges and saw that they didn't contain very many cells. I recognized that I was repeating the effort for each cell in the merged range, but felt that it would be plenty fast enough for the purpose at hand. And certainly easier to maintain than creating separate ranges for each of the groups of merged cells.
I agree that Parry's use of Union is a good simplification.
Brad
Cool.
So both goals accomplished (so far).
I'll let y'all know what she says.
By the way, she made a BIG contribution to the site--enough to pay the past month of advertising the VBA Training.
~Anne Troy
Hi Brad.Originally Posted by byundt
Anne, if there are cells with no data, then after the union do a intersect with used range, assuming the ranges are on the same sheet...
Set Rng = Union(Range("Rng3"), Range("Rng4"), Range("Rng5"), Range("Rng6")) Set Rng = Intersect(Rng, Rng.Parent.UsedRange)
Parry,
Use of Intersection is good advice, but not relevant in this situation. Anne is working on a spreadsheet form, and the used range includes cells containing label information above, below, right and left of the merged cells under discussion.
Brad