Changing Properties of a Range object after initial Dimensions ??

[Guest]

Hi there I don't know if this is posible or not but I would like to think so.

Sub Test()
Dim BaseDateRng As Range
Set BaseDateRng = Worksheets("Sheet1").Range("A210")
'This line below is wrong and doesn't work
Set BaseDateRng = BaseDateRng.Row - 1
End Sub
What I am trying to do is change just the Row property of my range object
without having to redeclare it as...
Set BaseDateRng = Worksheets("Sheet1").Range("A110")

I should be able to just alter reset or redim or something the "row"
property of my range object. Is this posible and if so what is the corect
syntax for doing it ??

Thanks, Dan

 

[Myrna Larson]

AFAIK, the Row property of a range is read-only. It's the absolute row number
on the worksheet.

Maybe you're looking for

Set BaseDateRng = BaseDateRng.Offset(-1, 0)

 

[Tim Williams]

If r is your range

Set r = r.Resize(r.Rows.Count - 1, r.Columns.Count).Offset(1, 0)

makes the range on row shorter and shifts it down one row.

Tim.

 

[Tom Ogilvy]

Sub Test()
Dim BaseDateRng As Range
Set BaseDateRng = Worksheets("Sheet1").Range("A210")
'This line below is wrong and doesn't work
Set BaseDateRng = BaseDateRng.Offset(-1, 0) _
.Resize(BaseDateRng.Rows.Count + 1)
Debug.Print BaseDateRng.Address
End Sub

produces

$A$1:$D$10

 

[Tim Williams]

If r is your range

Set r = r.Resize(r.Rows.Count - 1, r.Columns.Count).Offset(1, 0)

makes the range on row shorter and shifts it down one row.

...... which of course is *not* what was required (but hey, you might need
to do it some day).

Yim.