Variable Autofill

Posted by JAF on May 22, 2000 1:51 AM

I need to autofill some formulas down a sheet as part of a macro, but the number of rows in the sheet will vary from month to month.

When recorded, the macro returns the following code:
Range("J2:M2").Select
Selection.AutoFill Destination:=Range("J2:M4896")

The fill will always be copying the formulas in columns J to M, but the number of rows will vary.

Is it possible to do this as part of the macro?

JAF.

Posted by Ivan Moala on May 22, 2000 2:40 AM

Jaf
This is one way to do it....it worked for me.
Using your example;

Sub AutoFillRange()
Dim AutoFillRg As Range

Range("J2:M2").Select
Set AutoFillRg = Range(ActiveCell.Offset(0, -1), ActiveCell.Offset(0, -1).End(xlDown))
Selection.AutoFill Destination:=Range(ActiveCell.Address, AutoFillRg.Offset(0, 4).Address)

End Sub

HTH

Ivan