PDA

View Full Version : [SOLVED] need help please creating a quick VBA solution for auto filling a dynamic range



sschwant
06-16-2016, 01:22 PM
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

16412

cross posted: http://www.mrexcel.com/forum/excel-questions/947808-need-help-please-defining-dynamic-range-auto-fill-down-visual-basic-applications.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
06-16-2016, 01:55 PM
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
06-16-2016, 01:59 PM
Thanks mate! Interesting approach ... I may try that too. The other solution above also worked.

mdmackillop
06-16-2016, 02:03 PM
Of course you can always just double-click the fill button.