How to drag existing formulas down to the end of new data using VBA?

[Mr2017]

Hi

I have a spreadsheet where data is manually populated in columns A - C.

Column D, onwards is populated with formulas that use the data in columns A- C.

Is there a way to drag the existing formulas down, so that they include ALL the manual data in columns A - C? The number of rows populated will vary.

In the simple example below, I have data in cells A2: C4 (the numbers 1, 2, and 3 in cells A2:A4 then 4, 5, and 6 in cells B2:B4 and 7, 8 and 9 in cells C2:C4).

Cells D2 and E2 have formulas that add up the data in cells A2:C2 (both cells have the simple formula = A2+B2+C2 in this example).

However, there is new data in cells A3: C4. So is there a way to dynamically drag the existing formulas in D2 and E2 down, so that they cover the additional rows of data inserted in columns A - C? In this case, cells A3:C4?

I'd be very grateful for your thoughts.

Also, I'd like to be able to do it without having to insert a row at the top with formulas.

Eg I know this code will copy the formula from cell J1 and paste it into cell J13 and drag it until the end of the number of rows in column A. But I would prefer to have to avoid inserting a new row for formulas in the existing data.

Sub FormulaDrag()

Range("J1").Copy

Range("J13:J" & Range("A" & Rows.Count).End(xlUp).Row).PasteSpecial xlPasteFormulas


End Sub

Thanks in advance.

Title A	Title B	Title C	Title D (cells below are formula driver) Formula Column 1	Title E (cells below are formula driver) Formula Column 1
1​	4​	7​	12​	12​
2​	5​	8​
3​	6​	9​

 

[Mr2017]

Hi @RoryA

Thanks for posting the solution above, which worked.

I was just wondering if there is a way to amend the code so that it drags the formula down to the number of rows popualated in another sheet? Eg Sheet 1?

I have a new scenario where I need to drag the formula in cell A2 in Sheet 2 down to the number of rows populated in column A in Sheet 1.

The data in Sheet 1 will change every few days.

Please let me know if you'd like me to clarify the question further? I've attempted to amend the code, but I'm not sure where I should incorporate the Sheet 1 and Sheet 2 references below?

Thanks in advance.

Range("A2").Autofill destination:=Range("A2:A" & cells(rows.count, "A").end(xlup).row)

 

[RoryA]

Use sheets("sheet2").cells(rows.count, "A").end(xlup).row to get the last row from that sheet.

 

[Mr2017]

Hi Rory

Thanks for the prompt response.

I tried to run that, but got a subscript out of range error.

This is how I amended the code - have I made an error, somewhere?

I'd like to drag the formula in column A of Sheet 2 to as many rows as there are in column A of Sheet 1.

Range("A2").AutoFill Destination:=Range("A2:A" &

TIA

 

[RoryA]

Is the sheet actually called "sheet2"?

 

[Mr2017]

Hi Rory

Good spot - I'd renamed the "front of house" names.

I amended the code to this, and it worked:

Range("A2").AutoFill Destination:=Range("A2:A" & Sheet1.Cells(Rows.Count, "A").End(xlUp).Row)

Thanks for your help.