Weird range property behaviour

[Gareth Thackeray]

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

 

[Tom Ogilvy]

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.

 

[Don Guillett]

try
x = Range("rr").Cells(1, 1)to get the data in the top left

to select rows
With Range("rr")
.Range(Cells(1, 1), Cells(5, 1)).EntireRow.Select
End With

OR
With Range("rr")
..Rows("1:5").EntireRow.Select
End With

 

[Gareth Thackeray]

Hi Tom,

Thanks for the help Tom, I used Resize instead to accomplish what I wanted

I still don't quite understand what happens when you use rng.Range(cell1,
cell2). For your interest, a sub explaining my confusion is below:

Sub Temp()
Dim rng0 As Range, rng1 As Range, y As Integer
y = 3

Set rng0 = Worksheets(1).Cells(y, 1)
Set rng1 = rng0.Range(rng0(1), rng0(2))

Debug.Print "Top row rng0: " & rng0.row
Debug.Print "Top row rng1: " & rng1.row

End Sub

I would expect the top row in either case to be y, but instead the top row
of rng1 always seems to be 2y -1.

FYI, what I'm actually trying to do is to get a range that consists of rows
r0 to r1 of an existing range.

Best regards,

Gareth

 

[Tom Ogilvy]

Sub Temp()
Dim rng0 As Range, rng1 As Range, y As Integer
y = 3

Set rng0 = Worksheets(1).Cells(y, 1)
Set rng1 = Range(rng0(1), rng0(2))

Debug.Print "Top row rng0: " & rng0.Row
Debug.Print "Top row rng1: " & rng1.Row

End Sub

will give you the same top row.

Range(rng0(1), rng0(2))
is relative to the top left cell of the spread sheet.

rng0.Range(rng0(1), rng0(2))
is relative to the location of rng0

rng0 is A3
rng0(1) is A3
rng0(2) is A4

frm the immediate window:
set rng0 = cells(3,1)
? rng0.address, rng0(1).address, rng0(2).address
$A$3 $A$3 $A$4

Range("A3","A4") refers to A3:A4 - the addresses are absolute/relative to
A1

rng0.Range("A3","A4") says, from A3, step down 3 rows (counting A3 as the
first) and address a two cell vertical range.

 

[Gareth Thackeray]

Ahhh.

Now I see. Thanks very much Tom.

Best regards,

Gareth