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​

 

[RoryA]

You could use something like:

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

 

[AlanY]

try

Public Sub Copy()
Last_A = Cells(Rows.Count, "A").End(xlUp).Row
Last_D = Cells(Rows.Count, "D").End(xlUp).Row
If Last_A = Last_D Then Exit Sub
Range(Cells(2, 4), Cells(2, 5)).Copy _
Range(Cells(Last_D + 1, 4), Cells(Last_A, 5))
End Sub

 

[Mr2017]

Awesome!!

Thanks for the prompt response, @RoryA - your code worked!!

Thanks again!

 

[Mr2017]

Thanks @AlanY - that code worked, as well!!

Thank you!

 

[AlanY]

Thanks @AlanY - that code worked, as well!!

Thank you!

you're welcome

 

[Rebeccahelp needed]

Hi,
I have a similar issue.. I am using the below to copy my formula down in the Macro:

Range("C2").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-1]*2)"
Selection.AutoFill Destination:=Range("c2:c" & Range("b" & Rows.Count).End(xlUp).Row)
Range(Selection, Selection.End(xlDown)).Select

Work really well, however I have a variable number of lines in column B and when it is only one line it debugs - how can I stop this?
thanks

 

[AlanY]

Hi,
I have a similar issue.. I am using the below to copy my formula down in the Macro:

Range("C2").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-1]*2)"
Selection.AutoFill Destination:=Range("c2:c" & Range("b" & Rows.Count).End(xlUp).Row)
Range(Selection, Selection.End(xlDown)).Select

Work really well, however I have a variable number of lines in column B and when it is only one line it debugs - how can I stop this?
thanks

you can just get

On Error Resume Next

at the beginning

 

[Rebeccahelp needed]

Thank you! that worked...

 

[AlanY]

you're welcome