EntireRow extends the range to the entire row on the worksheets.

r(1)

will give you the top left cell of r
as will
r.cells(1,1)

if you want 5 cells in a single column based on the top left cell

r(1).Resize(5,1)

or using your approach:

set r = Range("Z20")
? Range(r.cells(1,1),r.cells(5,1)).Address
$Z$20:$Z$24

as an example, when you say
r.Range("A1"), that is the upper left corner of r. A1 is relative to r.

r.Range("Z10") is relative to where r is again, so it is 10 rows and 26
columns away. so you get what you describe as strange behavior.
--
Regards,
Tom Ogilvy

"Gareth Thackeray" wrote in message
...
Hi everybody,

I hope someone can shed some light on this very strange Excel 2000
behaviour.

I have a range object, r, and I would expect:

r.Range(r.Cells(1,1), r.Cells(5,1)).EntireRow to return a range whose
top-left cell was the top-left cell of r.

But instead, it returns a range whose top-left cell is the leftmost cell
of
the 4th row down of r.

And in fact, r.Range(r.Cells(-2,1), r.Cells(5,1)).EntireRow returns a
range
with the top-left cell that is the top-left cell of r.

Is the problem for this too obscure to see or too obvious to see?

Thanks for any help,

Gareth