Programmatically Erase Data in Merged Cells

[Anne Troy]

I keep getting errors "cannot change part of a merged cell"

[Zack Barresse]

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.

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]

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]

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.

[byundt]

Dreamboat,
Please send the workbook.
Brad

[Anne Troy]

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

[parry]

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]

Hi, parry! Thanks! Then....how do I tell it all those named ranges are my range, instead of just A1:A6?

[parry]

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]

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]

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]

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]

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]

I checked each of those named ranges and saw that they didn't contain very many cells.
Brad

Hi Brad.

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]

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