VBA Code for fill down

[James456]

Hello,

I've tried finding an answer but have been unsuccessful so far - I need someone to spell this out for me I think!

In the Macro I'm trying to put together, I want to enter a formula into cell B2 (so far so good) and I want to copy the formula down as far as the last non-blank cell in column A.

So if I were simply in a spreadsheet, I would double click on the drag down square in the bottom right hand corner of the cell selected. That is all I'm trying to do - but within a macro.

Hope that's making some sense and fingers crossed somebody can help. Apologies if this has been answered before.

Cheers
James

 

[VoG]

Try like this

Sub test()
Dim LR As Long
LR = Range("A" & rows.Count).End(xlUp).Row
Range("B2").AutoFill Destination:=Range("B2:B" & LR)
End Sub

 

[Jonmo1]

Welcome to the board..

If you already have the formula that you put in B2, call it "Myformula"

Try

Sub Test()
Dim lr As Long
lr = Cells(Rows.Count,"A").End(xlup).Row

'replace "MyFormula" with whatever formula you already have that works for B2
Range("B2:B" & lr).Formula = "MyFormula"
End sub

Hope that helps.

 

[James456]

Thank you guys, I tried both approaches and ran in to problems elsewhere in my code... Both I think the autofill part worked so thanks!