automating autofill across merged cells?

[Edward Scott]

hi,

I am trying to automate an autofill. I can usually do this just fine,
but this time it is across merged cells, which always causes an error.

in the spreadsheet, every three columns are merged (e.g. A1,B1,C1 are
merged; D1,E1,F1 are merged etc.). my code looks like this:



'find the last cell in the row with text in it
Set SourceRange = Range("C1").End(xlToRight)
'set the fill range
Set fillRange = Range("C1").End(xlToRight).Resize(1, 6)
'use autofill
SourceRange.AutoFill Destination:=fillRange



when I get to the autofill line I get a Run-time error 1004: "This
operation required the merged cells to be identically sized"

my merged cells are identically sized. I have tried selecting the
source and fill range to make sure they point to the right ranges,
which they do. I have tried other methods of selecting the same cells,
but it makes no difference.

can anyone help

 

[Kalpesh]

Hi,

I am sorry that I could not understand your scenario.

Are all your cells merged in triplets ?
i.e A1+B1+C1, D1+E1+F1 till IT1+IU1+IV1

What is the fillRange for ?
What is it that you are trying to achieve ?

Kalpesh

 

[Edward Scott]

yes, all my merged cells are in triplet, just as you describe

fillRange can usually be used to continue a pattern. If you are using
the Excel GUI and you have a pattern of formula across consecutive
cells, you can fill that pattern into neighbouring cells by clicking
the little square in the bottom-right hand corner and dragging it over
the target cells. (if my description is horrible, see the tutorial
here: http://www.mistupid.com/viewlets/excel/xlautofill.htm)

fillRange is the way to create this same event in Excel VBA. I can use
fillRange across single cells just fine, but when trying to do it
across merged triplets I get the error message describe above.

I would like to fill a pattern across my merged cells. I am able to do
so when explicitly naming the target cells e.g. "D1:F1" but not when I
am using a variable that refers to exactly the same set of cells e.g.
Range(myCell).Resize(1,3).

 

[Edward Scott]

oops... when I said "fillRange" in that last message I actually meant
autoFill.

fillRange just refers to the range that the autoFill is targeting.

 

[Edward Scott]

never mind. I solved my own problem in the end. the problem was that
the SourceRange only referred to the first of the three merged cells.
The code above should have looked like this:

Set SourceRange = Range("C1").End(xlToRight).Resize(1,3)
'set the fill range
Set fillRange = Range("C1").End(xlToRight).Resize(1, 6)
'use autofill
SourceRange.AutoFill Destination:=fillRange

 

[Kandra]

Just a suggestions, but merged cells can cause serious havok on your
spreadsheets. I would suggest that you always use Center Across format
on them. Much better... and you can create a button on your toolbar
for it.

Kandra

 

[Edward Scott]

yes, thank you. I have since rid my spreadsheet of all merged cells.
they are more trouble than they are worth.