Creating a dynamic autofill range in recorded code

Question

I'm trying to get a dynamic range in a recorded macro.
The data in column "O" is variable.
When I used the macro, VBA set it at the range of the specific worksheet.
Is there a way to make the autofill option variable to the amount of rows?

I used this code:

Range("O2").Select

Selection.AutoFill Destination:=Range("O2:O188")

Range("O2:O188").Select

I want the range to be dynamic.

When you say 'to the amount of rows', what do you mean? For instance, the rows of the column N:N will be good enough? — FaneDuru

FaneDuru FaneDuru · Accepted Answer · 2020-06-22T09:40:13

Try the next code, please. It uses column N:N as reference for the number of rows to be filled. No need to select anything:

Sub testAutoFill()
  Dim sh As Worksheet, lastRow As Long
  Set sh = ActiveSheet
  lastRow = sh.Range("N" & Rows.Count).End(xlUp).row
  sh.Range("O2").AutoFill Destination:=sh.Range("O2:O" & lastRow)
End Sub

The code can use the maximum rows of the used range, but in such a case, if the value to be filled down is a formula, the referenced column is good to be used. Otherwise, one of the neighbor column.

Creating a dynamic autofill range in recorded code

1 Answers