vb code to clearcontents of a merged cell

  • Thread starter Thread starter MitzDriver
  • Start date Start date
M

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.
 
Try setting its value to ""

worksheets("Somesheetnamehere").range("yourmergedrange").value = ""
 
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
 
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.
 
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
 
Hmm, it never occurred to me to try the MergeArea properties/methods out on
non-merged cells... yep, it works. Thanks for noting that.
 
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...).
 
For some reason, I think you could just use

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

Barb Reinhardt
 
Back
Top