vb code to clearcontents of a merged cell
 

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.

vb code to clearcontents of a merged cell
 

Try setting its value to ""

worksheets("Somesheetnamehere").range("yourmergedr ange").value = ""



MitzDriver wrote:

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

vb code to clearcontents of a merged cell
 

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" wrote:

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.

vb code to clearcontents of a merged cell
 

EXCELLENT!!! they both worked.

Thanks guys for the quick response.

"Dave Peterson" wrote:

Try setting its value to ""

worksheets("Somesheetnamehere").range("yourmergedr ange").value = ""



MitzDriver wrote:

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
.

vb code to clearcontents of a merged cell
 

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.

--
Rick (MVP - Excel)


"MitzDriver" wrote in message
...
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.

vb code to clearcontents of a merged cell
 

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 wrote:

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.

--
Rick (MVP - Excel)

"MitzDriver" wrote in message
...
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

vb code to clearcontents of a merged 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.

--
Rick (MVP - Excel)


"Dave Peterson" wrote in message
...
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 wrote:

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.

--
Rick (MVP - Excel)

"MitzDriver" wrote in message
...
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

vb code to clearcontents of a merged cell
 

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

Rick Rothstein wrote:

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

--
Rick (MVP - Excel)

"Dave Peterson" wrote in message
...
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 wrote:

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.

--
Rick (MVP - Excel)

"MitzDriver" wrote in message
...
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

--

Dave Peterson

vb code to clearcontents of a merged cell
 

For some reason, I think you could just use

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

Barb Reinhardt

"Patrick Molloy" wrote:

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" wrote:

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.