need help please creating a quick VBA solution for auto filling a dynamic range

[sschwant]

I need to copy down formulas in range S2:AH2. However, the number of rows these formulas are referencing in columns A:R, will always be variable. I know there's a way to use count or Dim a Variable range of rows ... but not sure how to implement or write the code.

Hopefully, the attached image below is visible.

Thanks!

Steve

dynamic range.jpg

cross posted: http://www.mrexcel.com/forum/excel-q...lications.html

This solved my problem:

Sub LastRow()
Dim lR As Long
lR = Range("A:R").Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
Range("S2:AH" & lR).FillDown
End Sub

[mdmackillop]

You can create a dynamic range name on the sheet using Formulas/Name Manager
e.g. Create a name "List" with the formula
=OFFSET(Sheet1!$A$1,1,0,COUNTA(Sheet1!$A:$A)-1,1)

You can then refer to that list from any routine

Sub Test()
Range("List").Offset(, 18).FillDown
End Sub

[sschwant]

Thanks mate! Interesting approach ... I may try that too. The other solution above also worked.

[mdmackillop]

Of course you can always just double-click the fill button.