Using defined names (of cells) in VBA to set a range

[d0rian]

I started with these 2 lines of VBA:

With Sheets("Soft")
.Range("R6:AW326").Copy

The problem is that the range I want to copy is going to change frequently as I add/remove rows from sheet 'Soft'. So I want to know how to alter the above code in 2 ways (not sure which is going to make more sense for me yet, so I'd like to know how to do it each way.

1) If I put into cell D1 the text of the range I want to copy, how do I get the VBA to use that text as the defined range. e.g. let's say I fill D1 with the text: "R8:AW100" (which will change dynamically as I add rows/cols). How can I make my VBA code use whatever's in cell D1 as the range to copy in line 2 above?

2) Let's say I name the cell I want to start the copy at (R6 in the example above) "xxx_start", and the end cell (AW326) "xxx_end"...how can I use those two defined names in the VBA code?

Thanks!

 

[Rick Rothstein]

1) If I put into cell D1 the text of the range I want to copy, how do I get the VBA to use that text as the defined range. e.g. let's say I fill D1 with the text: "R8:AW100" (which will change dynamically as I add rows/cols). How can I make my VBA code use whatever's in cell D1 as the range to copy in line 2 above?

With Sheets("Soft")
.Range(Range("D1").Value).Copy

2) Let's say I name the cell I want to start the copy at (R6 in the example above) "xxx_start", and the end cell (AW326) "xxx_end"...how can I use those two defined names in the VBA code?

With Sheets("Soft")
.Range("xxx_start", "xxx_end").Copy

 

[d0rian]

Thank you.