PDA

View Full Version : Solved: Programmatically Erase Data in Merged Cells



Anne Troy
09-18-2004, 11:23 AM
I keep getting errors "cannot change part of a merged cell"

Zack Barresse
09-18-2004, 12:14 PM
Nope you can't do that. You could use a selection instead.

cell.Select
Selection.ClearContents

There's also other ways around it. Let me know if you need something else. :yes

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. ;)

roos01
09-18-2004, 12:15 PM
when I used next line on the merged cell I retrieve the same error as you retrieve:
ActiveCell.ClearContents

but when using the following line it goes well:
ActiveCell = ""

perhaps this helps.
Jeroen

Anne Troy
09-18-2004, 12:50 PM
ActiveSheet.Unprotect Password:=""
Range("rng1").ClearContents
ActiveSheet.Protect Password:=""

Anne Troy
09-18-2004, 12:50 PM
When I do the = "", I get "does not support this property or method"

Anne Troy
09-18-2004, 01:06 PM
I can send the workbook to anyone who wants to help. :)

byundt
09-18-2004, 01:09 PM
Dreamboat,
Please send the workbook.
Brad

Anne Troy
09-18-2004, 01:11 PM
Sent!

Anne Troy
09-18-2004, 01:14 PM
Brad: I said it was the gray cells. And it is, except for the one at top-right. Leave that one alone.

Anne Troy
09-18-2004, 01:42 PM
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
09-18-2004, 01:43 PM
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 = True
End Sub

parry
09-18-2004, 02:22 PM
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

Anne Troy
09-18-2004, 02:28 PM
Hi, parry! Thanks! Then....how do I tell it all those named ranges are my range, instead of just A1:A6?

parry
09-18-2004, 03:17 PM
Hi Anne, change this line...
Set Rng = Range("A1:A6")
to this to join all the cells into one big range...
Set Rng = Union(Range("Rng3"),Range("Rng4"),Range("Rng5"),Range("Rng6"))
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.

parry
09-18-2004, 03:27 PM
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

Anne Troy
09-18-2004, 03:27 PM
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...

byundt
09-18-2004, 03:41 PM
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

Anne Troy
09-18-2004, 03:52 PM
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. :)

parry
09-18-2004, 04:07 PM
I checked each of those named ranges and saw that they didn't contain very many cells.
Brad
Hi Brad.:hi:

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)

byundt
09-18-2004, 05:17 PM
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