A Microsoft Excel forum. ExcelBanter

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Programming
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

Autofill Merged Cells



 
 
Thread Tools Display Modes
  #1  
Old March 4th 07, 04:21 AM posted to microsoft.public.excel.programming
howard63
external usenet poster
 
Posts: 3
Default Autofill Merged Cells

I am trying to program autofilling merged cells. I am inserting new rows
where columns B&C are merged. The inserted rows do not have B&C merged. I
then try to autofill these inserted rows where the source is merged. I'm
using the following code.

Set srcRange = cSheet.Range("B39:C39")
Set fillRange = cSheet.Range("B39:C45")
srcRange.AutoFill Destination:=fillRange

I can do it manually, but am having difficulty doing it with visual basic.
Thanks for any help.
Ads
  #2  
Old March 4th 07, 05:38 AM posted to microsoft.public.excel.programming
howard63
external usenet poster
 
Posts: 3
Default Autofill Merged Cells

I figured out what I was doing wrong. I was trying to fill a range that had
some merged cells and some not-merged cells. When I only filled the
non-merged cells, they autofilled properly and merged as well following the
source cell.

"howard63" wrote:

> I am trying to program autofilling merged cells. I am inserting new rows
> where columns B&C are merged. The inserted rows do not have B&C merged. I
> then try to autofill these inserted rows where the source is merged. I'm
> using the following code.
>
> Set srcRange = cSheet.Range("B39:C39")
> Set fillRange = cSheet.Range("B39:C45")
> srcRange.AutoFill Destination:=fillRange
>
> I can do it manually, but am having difficulty doing it with visual basic.
> Thanks for any help.

  #3  
Old March 4th 07, 05:52 AM posted to microsoft.public.excel.programming
Jay
external usenet poster
 
Posts: 671
Default Autofill Merged Cells

Hi Howard -

Sounds like you figured it out the problem... if you're filling or copying
from merged cells, the destination has to be merged as well (and be merged
over the same number of cells) or VBA will report a runtime error. I'm not
sure how you addressed just the unmerged cells in the destination range and
still got the sequence you wanted, but I'm not second-guessing something that
worked!

Below is the solution I usually use, and that is to merge all the cells in
the destination before pasting or autofilling.

For Each rw In cSheet.Range("B39:C45").Rows
If Not rw.MergeCells Then rw.Cells.Merge
Next rw

Set srcRange = cSheet.Range("B39:C39")
Set fillRange = cSheet.Range("B39:C45")
srcRange.AutoFill Destination:=fillRange

--
Jay


"howard63" wrote:

> I figured out what I was doing wrong. I was trying to fill a range that had
> some merged cells and some not-merged cells. When I only filled the
> non-merged cells, they autofilled properly and merged as well following the
> source cell.
>
> "howard63" wrote:
>
> > I am trying to program autofilling merged cells. I am inserting new rows
> > where columns B&C are merged. The inserted rows do not have B&C merged. I
> > then try to autofill these inserted rows where the source is merged. I'm
> > using the following code.
> >
> > Set srcRange = cSheet.Range("B39:C39")
> > Set fillRange = cSheet.Range("B39:C45")
> > srcRange.AutoFill Destination:=fillRange
> >
> > I can do it manually, but am having difficulty doing it with visual basic.
> > Thanks for any help.

  #4  
Old March 4th 07, 06:26 AM posted to microsoft.public.excel.programming
howard63
external usenet poster
 
Posts: 3
Default Autofill Merged Cells

Jay,
Thanks for the reply. What I did actually worked. The source range was 2
merged cells. All of the destination range was unmerged. When the code
autofilled, it also merged the cells in the destination range. Take care and
thanks again.
Howard

"Jay" wrote:

> Hi Howard -
>
> Sounds like you figured it out the problem... if you're filling or copying
> from merged cells, the destination has to be merged as well (and be merged
> over the same number of cells) or VBA will report a runtime error. I'm not
> sure how you addressed just the unmerged cells in the destination range and
> still got the sequence you wanted, but I'm not second-guessing something that
> worked!
>
> Below is the solution I usually use, and that is to merge all the cells in
> the destination before pasting or autofilling.
>
> For Each rw In cSheet.Range("B39:C45").Rows
> If Not rw.MergeCells Then rw.Cells.Merge
> Next rw
>
> Set srcRange = cSheet.Range("B39:C39")
> Set fillRange = cSheet.Range("B39:C45")
> srcRange.AutoFill Destination:=fillRange
>
> --
> Jay
>
>
> "howard63" wrote:
>
> > I figured out what I was doing wrong. I was trying to fill a range that had
> > some merged cells and some not-merged cells. When I only filled the
> > non-merged cells, they autofilled properly and merged as well following the
> > source cell.
> >
> > "howard63" wrote:
> >
> > > I am trying to program autofilling merged cells. I am inserting new rows
> > > where columns B&C are merged. The inserted rows do not have B&C merged. I
> > > then try to autofill these inserted rows where the source is merged. I'm
> > > using the following code.
> > >
> > > Set srcRange = cSheet.Range("B39:C39")
> > > Set fillRange = cSheet.Range("B39:C45")
> > > srcRange.AutoFill Destination:=fillRange
> > >
> > > I can do it manually, but am having difficulty doing it with visual basic.
> > > Thanks for any help.

 




Thread Tools
Display Modes

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

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Autofit Merged cell Code is changing the format of my merged cells JB Excel Discussion (Misc queries) 0 August 20th 07 02:12 PM
can autofill be used on merged cells? Debbie Excel Discussion (Misc queries) 2 June 27th 06 02:38 PM
how do i link merged cells to a merged cell in another worksheet. ibbm Excel Worksheet Functions 3 April 27th 06 11:40 PM
Autofill: Need to autofill one week block, (5) weekday only into cells. dstock Excel Discussion (Misc queries) 1 June 17th 05 08:21 PM
Sorting merged cellsHow do I sort merged cells not identically siz Laval Excel Worksheet Functions 1 November 3rd 04 09:40 PM


All times are GMT +1. The time now is 05:02 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
Copyright 2004-2014 ExcelBanter.
The comments are property of their posters.