Using VBA to autofill dynamic data table formulas

[Johnnyv015]

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.

 

[Larry Haydn]

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.

 

[Joe4]

Larry,

Just a little trick you can use. Instead of:

LastRow = Activesheet.range("A1048576").End(XlUp).Row

you can just use:

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

 

[Johnnyv015]

Legends!!!! Thank you both for the fast and perfect response!

This works great!

 

[Larry Haydn]

Larry,

Just a little trick you can use. Instead of:

LastRow = Activesheet.range("A1048576").End(XlUp).Row

you can just use:

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!

 

[Joe4]

Thank you very much Joe4!
I saved it in my little bag of tricks!

You are most welcome!