Help with Dynamically changing Range in Excel Macro

  • Thread starter Thread starter runsrealfast
  • Start date Start date
R

runsrealfast

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))
 
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)
 
NumRows = 1006
Range("R2").FormulaR1C1 = "=VALUE(RC[-1])"
Range("R2").AutoFill Destination:=Range("R2:R" & NumRows)
Click to expand...


I think this is what I was looking for
 
NumRows = 1006
Range("R2").FormulaR1C1 = "=VALUE(RC[-1])"
Range("R2").AutoFill Destination:=Range("R2:R" & NumRows)
Click to expand...


BTW. I tested it and it worked.

Thanks

John
 
Back
Top