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.

	a	b	c	d	e	f	g	h	i
1	a	$126			$118	$142	$141	$114	$94
2	b	$226			$177	$179	$218	$165	$165
3	c	$248			$260	$240	$260	$196	$196
4	d	$184			$182	$182	$243	$206	$206

<colgroup><col><col span="6"><col span="3"></colgroup><tbody>
</tbody>

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