vba to assemble cell address

[ThomasOES]

I would like a range that includes the number of rows +1 a cell value states. I've tried various versions of the code below.

'Range("C2", Range("C" & Range("MonitorTotal")+1).Value).Copy

As you can see I want the range from C2 to C and the value from the named cell "MonitorTotal" +1.

Thanks for any help

Tom

 

[Jonmo1]

Try

Range("C2:C" & Range("MonitorTotal")+1).Copy

 

[RoryA]

You were close:

Range("C2", Range("C" & Range("MonitorTotal").Value +1)).Copy

or just:

Range("C2").Resize(Range("MonitorTotal").Value).Copy

 

[Marcelo Branco]

I would like a range that includes the number of rows +1 a cell value states. I've tried various versions of the code below.

Maybe...
Range("C2").Resize(Range("MonitorTotal").Value + 1).Copy

M.

 

[ThomasOES]

I kept trying and finally stumbled into the code

Range("C2", "C" & 1 + Range("MonitorTotal").Value).Copy

Tom

 

[ThomasOES]

Thanks Rory.
Works perfectly. Even better would be a more direct way to copy the range. Is there a way to use Offset rather than adding to "MonitorTotal"?
Something like

Range("C2" , Offset(Range("MonitorTotal").Value), 0).Copy

In essence, using the cell value in "MonitorTotal" as the row offset property.

Thanks
Tom

 

[RoryA]

If your named range has the value 3, which range do you want to copy: C2:C4 or C2:C5? If it's the former, you could use the Resize version I posted.

 

[Marcelo Branco]

If your named range has the value 3, which range do you want to copy: C2:C4 or C2:C5? If it's the former, you could use the Resize version I posted.

If it's the later, try the code in post 4.

M.

 

[ThomasOES]

Thanks Rory

That operation will be repeated many times in my macro. From many locations. Keeping track of how many rows from the top of the sheet would have been a pain.

Tom

 

[Marcelo Branco]

Tom

You did not answer Rory's question in message 7. After all, should it be C2:C4 or C2:C5?

M.