Macro: Fill down then stop

[XLerator]

How do I create a code in which after inserting a column and typing in a formula, it FILLS IT DOWN, but STOPS with the LAST row of text---each day I run the macro the number of rows would be different, so the formula would have to stop on that row...the rows below it are all blank.

Is there a way?

[Tom Schreiner]

Hi
This little macro would enter your formula in C1, count used rows by Column B, and then fill down column C.

Edit to suit by replacing the formula with the one you need and also the column used for criteria/row count.

Sub FillDown()
Dim LastRow As Long
Range("C1").Formula = "=Sum(A1+B1)"
LastRow = Range("B1:B" & Range("B1").End(xlDown).Row).Rows.Count
Range("C1" & ":C" & LastRow).FillDown
End Sub

Tom

P.S.
I did not read your post as clearly as I should have. You are adding a column.
I would use another line of code with this variable to find the column you need to place the formula in:

LastColumn = Cells(1, Columns.Count).End(xlToLeft).Column

If you need more detailed help, please re-post.

Thanks,
Tom


[ This Message was edited by: TsTom on 2002-04-15 21:54 ]

[Colo]

Hello XLerator. Hi Tom!
Here is similar way.
The layout I assumed are the same as Tom's.
Formulas would be written to the specific range directly.

Sub Test()
Range("C1", Range("B65536").End(xlUp).Offset(, 1)).Formula = "=A1+B1"
End Sub