Help with Dynamically changing Range in Excel Macro
 

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

Help with Dynamically changing Range in Excel Macro
 

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

Help with Dynamically changing Range in Excel Macro
 

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

Help with Dynamically changing Range in Excel Macro
 

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

Help with Dynamically changing Range in Excel Macro
 

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