vb code to clearcontents of a merged cell

[MitzDriver]

I have a small macro that needs to clearcontents of a merged cell. However, I
get the 1004 error of "Cannot clear contents of a merged cell". Does anyone
have a work around or some code that will unmerge, clear and merge?
Thanks in advance for any suggestions.

 

[Dave Peterson]

Try setting its value to ""

worksheets("Somesheetnamehere").range("yourmergedrange").value = ""

 

[Patrick Molloy]

you could make this a sub and pass a range to it

Sub clearoff()
Dim r As Range
Set r = Range("C6")
If r.MergeCells Then
r.MergeArea.Select
r.MergeArea.UnMerge
r.ClearContents
r.Merge
Else
r.ClearContents
End If

End Sub

 

[MitzDriver]

EXCELLENT!!! they both worked.

Thanks guys for the quick response.

 

[Rick Rothstein]

You don't need to unmerge anything. It might have helped if you showed us
your code so we could see how you got to your merged cell (that is, were you
iterating through a range of cells, one cell at a time, and found a problem
when you hit a member cell in the merge). Anyway, perhaps you can adopt this
snippet to your needs....

For Each Cell in Range("A1:M100")
If Cell.MergeCells Then
Cell.MergeArea.ClearContents
Else
Cell.ClearContents
End If
Next

This code will clear the contents of all cells, merged or not, within
A1:M100.

 

[Dave Peterson]

You don't even have to worry about checking first.

Dim cell As Range
For Each cell In Range("A1:M100")
cell.MergeArea.ClearContents
Next cell

 

[Rick Rothstein]

Hmm, it never occurred to me to try the MergeArea properties/methods out on
non-merged cells... yep, it works. Thanks for noting that.

 

[Dave Peterson]

And just because Gord hasn't appeared in this thread...

I do my best to avoid the use of merged cells. They mess up lots of things
(sorting, filtering, copy|pasting...).

 

[Barb Reinhardt]

For some reason, I think you could just use

r.Mergarea.clearcontents whether the mergarea.count = 1 or 10.

Barb Reinhardt