Define a Range based on a Range object

[Terry]

Given a Range object (e.g: set xRange = Range("C110") )

And given xRow = 5 , which stands for Row 5 (absolute Row index)

How to define a range "yRange" based on "xRange" so that yRange is
Range("C510") ?

 

[Rick Rothstein]

I just posted a response to this same question over in the Microsoft forum
(I'm assuming you posted it there as the ranges look familiar).

 

[Terry]

I just posted a response to this same question over in the Microsoft forum
(I'm assuming you posted it there as the ranges look familiar).

yes thanks

 

[CompleteNewb]

Can you guys either copy the response here or give a link to the post in the
other newsgroup? I searched the Microsoft forums for both you guys as
authors, as well as looking for the subject line and the range reference
text, and found nothing.

And this seems like a question that's totally related to things I'm having
issues with now.

Thanks.

I just posted a response to this same question over in the Microsoft forum
(I'm assuming you posted it there as the ranges look familiar).

yes thanks

 

[Rick Rothstein]

Sorry for the delay in responding (I don't visit the newsgroups that often
anymore). Here is a link to the thread I mentioned...

http://social.answers.microsoft.com...e85f7471#fbae741f-47b9-42e9-80b7-d22f6dca8a92

but to save you the time of searching it out, here is what I responded over
there...

Consider this...

Dim xRange As Range, yRange As Range, NewRowBeginning As Long
NewRowBeginning = 5
Set xRange = Range("C110")
Set yRange = Intersect(xRange, Range(NewRowBeginning & ":" & Rows.Count))
'
' Test to see if the range came out right
'
If Not yRange Is Nothing Then
MsgBox yRange.Address(0, 0)
Else
MsgBox "The specified new row beginning is located below the specified
range"
End If