PDA

View Full Version : Copying Down Formula using VBA



brennaboy
08-20-2010, 08:21 AM
Hi,

I have a worksheet. The left half of the worksheet Cols A - W is populated with 3000 odd rows of data.

Range V2:AI2 has a row if formulas.

I want to write a macro that will copy down this row of formulas down to the bottom.

The way I thought I could do it was to use:

r = Application.CountA(Range("A:A"))

to count the number of rows of data and then use:

Range("W2:AI2").Select
Selection.AutoFill Destination:=Range("W2:AIr"), Type:=xlFillDefault
Range("W2:AIr").Select
ActiveSheet.Calculate

I am getting a debug because AIr is not right - what is the right way to express this OR is there a better way to do this?

Cheers,

B.

GTO
08-20-2010, 08:57 AM
Not sure if this will give the results you are looking for, but as to how to refer to the range:

Selection.AutoFill Destination:=Range("W2:AI" & r), Type:=xlFillDefault