How to autofill formula for known number of columns but variable number of rows in excel macro

Vote count:

0

I writing a macro within which I need to autofill some rows with formulas, across multiple columns.

The number of columns is fixed, but each time the macro runs, the number of rows is variable. I use the "record macro" function and the current macro only ever fills my rows to row 16. Below is the code:

Range("D3:P3").Select
Selection.AutoFill Destination:=Range("D3:P16")

I obviously need to change the "P16" to something dynamic.

I have tried to use the following:

Dim LR As Long
LR = Range("D3:P3" & Rows.Count).End(xlUp).Row
Range("B3:P3").AutoFill Destination:=Range("B3:P" & LR)

I am unsure whether the "Dim LR as Long" has to be placed at the very beginning of my macro - or can it just be placed anywhere? I am getting an error anyway with what i attempted above giving me an "autofill selectio error" (sorry i cant remember the exact error message.

Would someone be able to point me in the right direction?

asked 1 min ago

user3253628

17