| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 . |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| VBS Script and ClearContents -- Code Throws An Error | Excel Programming | |||
| Autofit Merged cell Code is changing the format of my merged cells | Excel Discussion (Misc queries) | |||
| Find * in a cell & ClearContents | Excel Programming | |||
| how do i link merged cells to a merged cell in another worksheet. | Excel Worksheet Functions | |||
| clearcontents, worksheet_change, cell validation | Excel Programming | |||
Linear Mode
