ExcelBanter - Weird range property behaviour

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - Weird range property behaviour (https://www.excelbanter.com/excel-programming/315391-weird-range-property-behaviour.html)

Weird range property behaviour

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

Weird range property behaviour

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

Weird range property behaviour

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

Weird range property behaviour

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.

--
Regards,
Tom Ogilvy

"Gareth Thackeray" wrote in message
...
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

Weird range property behaviour

Ahhh.

Now I see. Thanks very much Tom.

Best regards,

Gareth

"Tom Ogilvy" wrote in message
...
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.

--
Regards,
Tom Ogilvy

"Gareth Thackeray" wrote in message
...
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