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

 

[Tody03]

how i change your code to be aplicable to columns.
i have the following table.

[TABLE="width: 570"]
<colgroup><col><col span="6"><col span="3"></colgroup><tbody>[TR]
[TD][/TD]
[TD]a[/TD]
[TD]b[/TD]
[TD]c[/TD]
[TD]d[/TD]
[TD]e[/TD]
[TD]f[/TD]
[TD]g[/TD]
[TD]h[/TD]
[TD]i[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD="align: left"]a[/TD]
[TD="align: right"]$126[/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]$118[/TD]
[TD="align: right"]$142[/TD]
[TD="align: right"]$141[/TD]
[TD="align: right"]$114[/TD]
[TD="align: right"]$94[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD="align: left"]b[/TD]
[TD="align: right"]$226[/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]$177[/TD]
[TD="align: right"]$179[/TD]
[TD="align: right"]$218[/TD]
[TD="align: right"]$165[/TD]
[TD="align: right"]$165[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD="align: left"]c[/TD]
[TD="align: right"]$248[/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]$260[/TD]
[TD="align: right"]$240[/TD]
[TD="align: right"]$260[/TD]
[TD="align: right"]$196[/TD]
[TD="align: right"]$196[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD="align: left"]d[/TD]
[TD="align: right"]$184[/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]$182[/TD]
[TD="align: right"]$182[/TD]
[TD="align: right"]$243[/TD]
[TD="align: right"]$206[/TD]
[TD="align: right"]$206
[/TD]
[/TR]
</tbody>[/TABLE]

I do not know how many lines will be in the table, i do not know how many columns will be in the table.
I want to write a formula at the end of column b. i want to copy the same formula, on the same line, to column c and all the way to the right till the end of the table.
I tried as you suggested to record a macro but i get fix line number and columns.
Thanks

 

[Joe4]

There is a line of code in my post up in reply 2 that shows you how to find the last row with data, looking at a particular column.

To find the last column with data, when looking at a particular row, you can do it like this:

'Find last column with data in row [COLOR=#ff0000]1[/COLOR]
lCol = Cells([COLOR=#ff0000]1[/COLOR], Columns.Count).End(xlToLeft).Column

 

[Tody03]

I will try it tomorrow. thanks joe4