Using VBA to autofill dynamic data table formulas

Johnnyv015

New Member
Joined
Sep 8, 2014
Messages
11
Hello, I am looking to create a VBA code that will work as follows:
I paste new data set to bottom of old data set and click button, VBA then selects the rows with formulas and autofills them to the new bottom of the data set.


Currently my data set is located in 1-6700 and Columns A - Q; formulas range from columns S - BE.



I am looking to find the bottom of the data table once updated data has been applied then auto fill formulas from Columns S- BE to the bottom of the new data set.


Roadblocks: Formula starting and ending row changes with every data update.
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

Larry Haydn

Board Regular
Joined
Jul 18, 2019
Messages
138
Office Version
  1. 365
Platform
  1. Windows
1. To find the last pasted data row:
Choose a row that definitely have data in all rows.
Let's say Column A will definitely have data in all rows.
You can find the last row as follows:

Dim LastRow as Long, LastFormulaRow As Long
LastRow = Activesheet.range("A1048576").End(XlUp).Row

2. Next, find the last row that has formula (and therefore have data),
using the same code above (but use a different column, for example, Column S

LastFormulaRow = Activesheet.range("S1048576").End(XlUp).Row
Activesheet.Range("A" & LastFormulaRow & ":BE" & LastFormulaRow).AutoFill Range("A" & LastFormulaRow & ":BE" & LastRow)

Hope this works for you.
 
Solution

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
58,264
Office Version
  1. 365
Platform
  1. Windows
Larry,

Just a little trick you can use. Instead of:
VBA Code:
LastRow = Activesheet.range("A1048576").End(XlUp).Row
you can just use:
VBA Code:
LastRow = Activesheet.Cells(Rows.Count,"A").End(XlUp).Row

There are two advantages to that:
1. You do not need to remember or figure out how many rows are in Excel
2. It will also work on old versions of Excel (xls) that have less rows available
 

Larry Haydn

Board Regular
Joined
Jul 18, 2019
Messages
138
Office Version
  1. 365
Platform
  1. Windows
Larry,

Just a little trick you can use. Instead of:
VBA Code:
LastRow = Activesheet.range("A1048576").End(XlUp).Row
you can just use:
VBA Code:
LastRow = Activesheet.Cells(Rows.Count,"A").End(XlUp).Row

There are two advantages to that:
1. You do not need to remember or figure out how many rows are in Excel
2. It will also work on old versions of Excel (xls) that have less rows available
Thank you very much Joe4!
I saved it in my little bag of tricks!
 

Forum statistics

Threads
1,136,445
Messages
5,675,899
Members
419,591
Latest member
mersanko

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