I have a macro that contains the following code:

Range("R2").Select
ActiveCell.FormulaR1C1 = "=VALUE(RC[-1])"
Range("R2").Select
Selection.AutoFill Destination:=Range("R2:R1006")

What I am doing is simply creating a formula in cell R2 (it will
always be in R2) and then auto filling to the last populated row. I am
struggling on how to change the part of the code that says
"Range("R2:R1006")". Every time this macro runs the number of rows
will not be 1006. How is it that I change this? TIA



John

Depends how you determine 1006. Lets assume it is the last populated row in
column Q. The you can do it kinda like this...

Range("R2").FormulaR1C1 = "=VALUE(RC[-1])"
Range("R2").AutoFill Destination:=Range("R2", _
Cells(Rows.Count, "Q").End(xlUp).Offset(0, 1))

--
HTH...

Jim Thomlinson


"runsrealfast" wrote:

I have a macro that contains the following code:

Range("R2").Select
ActiveCell.FormulaR1C1 = "=VALUE(RC[-1])"
Range("R2").Select
Selection.AutoFill Destination:=Range("R2:R1006")

What I am doing is simply creating a formula in cell R2 (it will
always be in R2) and then auto filling to the last populated row. I am
struggling on how to change the part of the code that says
"Range("R2:R1006")". Every time this macro runs the number of rows
will not be 1006. How is it that I change this? TIA



John

NumRows = 1006
Range("R2").FormulaR1C1 = "=VALUE(RC[-1])"
Range("R2").AutoFill Destination:=Range("R2:R" & NumRows)


--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"runsrealfast" wrote in message
oups.com...
I have a macro that contains the following code:

Range("R2").Select
ActiveCell.FormulaR1C1 = "=VALUE(RC[-1])"
Range("R2").Select
Selection.AutoFill Destination:=Range("R2:R1006")

What I am doing is simply creating a formula in cell R2 (it will
always be in R2) and then auto filling to the last populated row. I am
struggling on how to change the part of the code that says
"Range("R2:R1006")". Every time this macro runs the number of rows
will not be 1006. How is it that I change this? TIA



John

On Jul 16, 2:56 pm, "Bob Phillips" wrote:
NumRows = 1006
Range("R2").FormulaR1C1 = "=VALUE(RC[-1])"
Range("R2").AutoFill Destination:=Range("R2:R" & NumRows)


I think this is what I was looking for

On Jul 16, 2:56 pm, "Bob Phillips" wrote:
NumRows = 1006
Range("R2").FormulaR1C1 = "=VALUE(RC[-1])"
Range("R2").AutoFill Destination:=Range("R2:R" & NumRows)


BTW. I tested it and it worked.

Thanks

John