Macro that stop when it finds a blank cell in Row A

[fishingsantafe]

Hi

I would like to write a macro that would copy formula in B2 and then copy it down row B until it finds that a cell in row A is blank. This report changes all the time so I never know how much data is in row A. So I want the macro to stop copying the formula down row B when it finds a blank cell in Row A.

I tried the Do Until but it did not do anything unless I had something wrong. I recorded the intial copy B2 to B3 to see what it wrote. I tried an if statement also.

Any help would be apprieciated. I have Excel 2003.

 

[Jonmo1]

Sub formulafill()
LR = Cells(Rows.Count, "A").End(xlup).row
Range("B2:B" & LR).FillDown
End Sub

 

[ravishankar]

Hi
Paste the following codes in the macro window ( Alt F8)

x = Cells(Rows.Count, 1).End(xlUp).Row
a = "B2:B" & x
Cells(2, 2).Select
    Selection.AutoFill Destination:=Range(a), Type:=xlFillDefault

run the macro
Ravi

 

[shippey121]

you know you can just double click the fill handle to do this, the fill handle is the little black square at the bottom right of the active cell,

HTH

 

[fishingsantafe]

Thanks for responding

Thanks Shippey121 I did nit realize the auto fill bar had this function,

Thanks Ravishankar and jonmo1 for the code. I will use them in the future and keep it for examples