Simple Macro Stuff - changing autofill

[AUSTIN1]

Hi

I am learning the basics of macros and am constructing a macro to format a spreadsheet. In the extract below my macro is filling a formula down on a range of cells R2:R54, unfortunately the number of rows varies from day to day so I would like to change this phrase so it fills down column R exactly how many rows are populated in column Q to the left hand side (or any other column from A onwards) so it fills down the correct depth every day

Selection.AutoFill Destination:=Range("R2:R54"), Type:=xlFillDefault

Hope someone can advise on what is quite a basic question

Kind Regards
Austin

[Bryan Hessey]

Hi

I am learning the basics of macros and am constructing a macro to format a spreadsheet. In the extract below my macro is filling a formula down on a range of cells R2:R54, unfortunately the number of rows varies from day to day so I would like to change this phrase so it fills down column R exactly how many rows are populated in column Q to the left hand side (or any other column from A onwards) so it fills down the correct depth every day

Selection.AutoFill Destination:=Range("R2:R54"), Type:=xlFillDefault

Hope someone can advise on what is quite a basic question

Kind Regards
Austin

Hi,

something along the lines of

qLastRow = ActiveSheet.Range("Q65536").End(xlUp).Row

then

Selection.AutoFill Destination:=Range("R2:R" & qLastRow), Type:=xlFillDefault

should suffice.

hth
---

[AUSTIN1]

thx very much