Copy a range of Array Formula using a macro.

[GAJITCS]

I have seen a few articles here about copying an Array Formula using a macro, but none for copying multiple array formulas.

I have a part populated sheet where the number of rows can vary.
Columns A:J are populated.
Cells K6:AT6 contain a mixture of Formulas and Array Formulas. I need to be able to copy these, preferably in one go, from their location in row K, down to the last row populated in column J.

Is this possible?

Many thanks.

 

[pgc01]

Hi

If I understand correctly you want to copy the formulas in K6:AT6 down for ex. till K100:AT100

Try using the Range.FillDown Method.
It will copy all the formulas (array or not) down.

 

[GAJITCS]

Hi

If I understand correctly you want to copy the formulas in K6:AT6 down for ex. till K100:AT100

Try using the Range.FillDown Method.
It will copy all the formulas (array or not) down.

Thank you.

I now have
Range("K6:AS39").Filldown

and this works for the specified rows, but I need to fill the number of rows without knowing how many there will be. Sometimes, row 39 may be the last row, at other times it could be row 10 or 1234.

How can I adjust this FillDown formula to compensate for those situations?

 

[pgc01]

hi

What you posted is equivalent to:

Dim lRow As Long

lRow = 39

Range("K6:AS" & lRow).FillDown

This means that you just have to know how to calculate lRow

If you have problems finding the right value for lRow post back.
In that case be clear about where the data ends:
- column J has some values and then empty cells. You want to fill the formulas till the row of the last cell in J with a value
or
- column J has formulas that can return a value or null strings. You want to fill the formulas till the row of the last cell in J with a value different from a null string

 

[GAJITCS]

Thank you everyone.

The following solved my problem.

Sub Fill_Down()
Dim LastRow As Long
LastRow = Cells(Cells.Rows.Count, "A").End(xlUp).Row
Range("K6:AS" & LastRow).FillDown
End Sub