Hi Ken,

Try:

Dim Rng as Range
Set Rng = Worksheets(2).Range(2A5").CurrentRegion


---
Regards,
Norman



"Ken Loomis" wrote in message
...
I need to get the values from a range of cells into an array. The range
will always start in the same cell on the sheet where it resides and it
will always be 3 cells wide. But it can grow in length.

Right now I get the values into the array with this statement:

Dim ReplaceArray As Variant

ReplaceArray = Worksheets(2).Range("a5:c6")

and that works fine as long as the range of cells containing the data is
static. Right now that works ok since I can manually change things as
needed, but I'd like to automate this and give the users the ability to
add to that list.

If I need to, I can increment a cell value on worksheet 2 that would be
the number of rows in the range, but I don't even know how to refer to the
range that way. and, there must be a better, more dynamic (and elegant)
way to do this. There is nothing on worksheet 2 below or to the right of
the last cell in the range, i.e. "C6" in the example above.

Thanks for any help on this.

Ken Loomis