View Single Post
  #3   Report Post  
Posted to microsoft.public.excel.programming
Jay Jay is offline
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.