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

Mr2017

Well-known Member
Joined
Nov 28, 2016
Messages
634
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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.

VBA Code:
Sub FormulaDrag()

Range("J1").Copy

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


End Sub

Thanks in advance.

Title ATitle BTitle CTitle D (cells below are formula driver) Formula Column 1Title E (cells below are formula driver) Formula Column 1
1​
4​
7​
12​
12​
2​
5​
8​
3​
6​
9​
 

Mr2017

Well-known Member
Joined
Nov 28, 2016
Messages
634
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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.


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

Some videos you may like

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,414
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Use sheets("sheet2").cells(rows.count, "A").end(xlup).row to get the last row from that sheet.
 

Mr2017

Well-known Member
Joined
Nov 28, 2016
Messages
634
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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.

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


TIA
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,414
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Is the sheet actually called "sheet2"?
 

Mr2017

Well-known Member
Joined
Nov 28, 2016
Messages
634
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,126,928
Messages
5,621,642
Members
415,849
Latest member
PhoenixRising2015

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top