flexible range

[Tody03]

I have a table with one column A with several lines. The number of lines in column A varies from time to time.
I wrote a macro (see below) that will write a formula in B1 and drag it down from B1 until the last used cell in column A.
In the attached macro the range is limited until B6. How can i make the range (B1:B6) flexible so that the last cell in B will match to the last cell in column A.

Range("B1").Select
ActiveCell.FormulaR1C1 = "=RC[1]+2"
Range("B1").Select
Selection.AutoFill Destination:=Range("B1:B6")
Range("B1:B6").Select
End Sub

thanks
tody

 

[Fluff]

You can replace all that code with

   Range("B1", Range("A" & Rows.Count).End(xlUp).Offset(, 1)).FormulaR1C1 = "=rc[1]+2"

 

[Joe4]

Try this:

Dim lr as Long

'Find last row in column A
lr = Cells(Rows.Count,"A").End(xlUp).Row
'Apply formula to column B
Range("B1:B" & lr).[COLOR=#333333]FormulaR1C1 = "=RC[1]+2"[/COLOR]

 

[mumps]

Already answered.

 

[Tody03]

Thanks for your quick response. It is working.
I have another question how to write a formula instead rc[1]+2 i want to fill the column with "=sum(b2:z2)"
Thanks

 

[Tody03]

Hello Joe4
Thanks for your quick response. Although I do not understand the syntax - It is working.
I have another question how to write a formula instead rc[1]+2 i want to fill the column with "=sum(b2:z2)"
Thanks

 

[MARK858]

Assuming you want the formula to increment then...

Range("B1:B" & lr).Formula = "=Sum(B2:Z2)"

If you don't want it to increment then

Range("B1:B" & lr).Formula = "=Sum($B$2:$Z$2)"

Expect a circular reference error in the 2nd row of the non-incrementing formula.

 

[Tody03]

Hello mark858. i feel stupid to ask simple questions but you helped me. thanks tody

 

[Joe4]

Also note, that in order to get formulas in the "R1C1" format, all you have to do is to turn on the Macro Recorder and record yourself entering the formula into ones of the cells.
Then, stop the recorder and view the code it recorded. It will give you the formula you need in R1C1 format.

 

[Tody03]

Also note, that in order to get formulas in the "R1C1" format, all you have to do is to turn on the Macro Recorder and record yourself entering the formula into ones of the cells.
Then, stop the recorder and view the code it recorded. It will give you the formula you need in R1C1 format.

Thanks i will follow your instructions