PDA

View Full Version : Solved: Create a marco that will autofill a formula but the range is variable



annemarie
08-13-2009, 11:18 PM
Hi there,

I am creating a macro where in a certain file will be opened then the data on that workbook will be processed automatically. But the problem is the data range varies in each work book, how can I make the range variable? I attached a sample code, I want the N2:Y2434 to be variable, its range would depend on the range of data from cell A2 to L2, say the range of A2 will be the reference for N2.

' Drag down cell formula
Range("N2:Y2").Select
Selection.AutoFill Destination:=Range("N2:Y2434"), Type:=xlFillDefault

I would really appreciate you help.
Thanks in advance.

annmarie:think:

MaximS
08-13-2009, 11:41 PM
try something like that:


Sub fill()

Dim LR As Long

'To define last row you want to fill - change A to column letter
'which will always contain some data
LR = Range("A" & Rows.Count).End(xlUp).Row

Range("N2:Y2").Copy Destination:=Range("N3:Y" & LR)

End Sub

annemarie
08-13-2009, 11:53 PM
Awesome! Thanks for the help..