copy range of cells using VB

[Stuart]

I need to copy a range of cells, but I don't know the cell I'm in.

For example, I may be cell "A6". I want to select and copy X number
of cells down. Every example shows 'Range("A6:A45").Select.' What I
need is something like 'Range(fromThisCell:toThisCell).Select' Can I
use the Offset command somehow to select a relitive number of cells?

I have two files open, and I want to copy a range of cells from one
sheet to the next. The problem is the range of data will always start
in row 6, but the columns will vary.

Any help with this will be greatly appreciated. If I did not present
my question clearly, please e-mail me and I will elaborate.

 

[Bob Phillips]

You can use Resize

Activecell.Resize(6,1)

is 6 rows down from, including, the active cell.

Or Ramge("A6").Resize(6,1)

 

[Stuart Sigafoos]

Bob,

Thanks for the quick responce.

The ActiveCell.Resize did not work; VB showed a 'compile error
=expected' and I could not figure out what I was missing.

But, using VB Help, I looked up the Resize function, and found that
"Selection.Resize(rangeToCopy, 1).Select" would in fact do exactly what
I wanted (and works!)

Again, thanks for pointing me in the right direction!

Stuart

 

[Bob Phillips]

Stuart,

I just tried to show you the technique, you have to set something to that
range, such as

Set myRange = ActiveCell.Resize(6,1)

which returns you a range object that you can work with.

I tend to avoid select like the plague if I can, it is slow and usually not
necessary.