PC Review


Reply
Thread Tools Rate Thread

Deleting contents from merged cells from VBA

 
 
=?Utf-8?B?QVZS?=
Guest
Posts: n/a
 
      18th Jul 2007
I have named 2 merged cells in a spreadsheet, say A1 & B1. The merged cell
is named "range1". I am trying to clear the contents from VBA code, using:
Range("range1").ClearContents
I get an error that says "Cannot change part of a merged cell"
Is there a simple workaround?
Assume I only know the range name and not the component cells.

Thanks
 
Reply With Quote
 
 
 
 
Dave Peterson
Guest
Posts: n/a
 
      18th Jul 2007
try:

Range("range1").value = ""
or
Range("range1").mergearea.ClearContents

AVR wrote:
>
> I have named 2 merged cells in a spreadsheet, say A1 & B1. The merged cell
> is named "range1". I am trying to clear the contents from VBA code, using:
> Range("range1").ClearContents
> I get an error that says "Cannot change part of a merged cell"
> Is there a simple workaround?
> Assume I only know the range name and not the component cells.
>
> Thanks


--

Dave Peterson
 
Reply With Quote
 
=?Utf-8?B?QVZS?=
Guest
Posts: n/a
 
      18th Jul 2007
..mergearea worked perfectly. Thanks

"Dave Peterson" wrote:

> try:
>
> Range("range1").value = ""
> or
> Range("range1").mergearea.ClearContents
>
> AVR wrote:
> >
> > I have named 2 merged cells in a spreadsheet, say A1 & B1. The merged cell
> > is named "range1". I am trying to clear the contents from VBA code, using:
> > Range("range1").ClearContents
> > I get an error that says "Cannot change part of a merged cell"
> > Is there a simple workaround?
> > Assume I only know the range name and not the component cells.
> >
> > Thanks

>
> --
>
> Dave Peterson
>

 
Reply With Quote
 
Incidental
Guest
Posts: n/a
 
      19th Jul 2007
Hi AVR

The following is just modified code from excel help

Option Explicit
Dim Ma As Range

Private Sub CommandButton1_Click()

Set Ma = Range("Range1").MergeArea

If Range("Range1").MergeCells Then

Ma.ClearContents

End If

End Sub

Hope it helps

S

 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
How do I delete merged cells in Excel without deleting the final d aries68mc Microsoft Excel Misc 5 16th Mar 2010 04:42 PM
row height increase automatically to fit contents of merged cells =?Utf-8?B?SkI=?= Microsoft Excel Misc 1 22nd May 2007 07:37 PM
Clear Contents won't work on merged cells =?Utf-8?B?QXJ0aHVySg==?= Microsoft Excel Programming 2 20th Feb 2006 05:23 PM
Clearing the Contents of Merged Cells prizm1 Microsoft Excel New Users 2 10th Sep 2005 04:42 AM
Re: Clearing the contents of merged cells Doug Glancy Microsoft Excel Programming 0 23rd Apr 2004 08:54 PM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 07:07 PM.