Return intesected range
 

I am using Office 2003 on Windows XP.

My code dynamically returns certain contiguous columns and certain
continguous rows, say "C:E" and "5:10" for example, in two different
variables as string addresses.

I need to return the address of the intersection of these two ranges: C5:E10
Is it possible to do this without parsing the range addresses and without
actually changing the user's current selection? If so, could someone please
post a one or two line solution? I've been trying to use the "Intersect"
method without success.

Thanks much in advance.

Return intesected range
 

strAddress = Intersect(Range(Var1),Range(Var2)).Address(False,F alse)

HTH,
Bernie
MS Excel MVP


"XP" wrote in message
...
I am using Office 2003 on Windows XP.

My code dynamically returns certain contiguous columns and certain
continguous rows, say "C:E" and "5:10" for example, in two different
variables as string addresses.

I need to return the address of the intersection of these two ranges: C5:E10
Is it possible to do this without parsing the range addresses and without
actually changing the user's current selection? If so, could someone please
post a one or two line solution? I've been trying to use the "Intersect"
method without success.

Thanks much in advance.

Return intesected range
 

Sub test()
Dim iRange As Range
Set iRange = Intersect(Range("C:E"), Range("5:10"))
Debug.Print iRange.Address
End Sub

HTH
--
AP

"XP" a écrit dans le message de
...
I am using Office 2003 on Windows XP.

My code dynamically returns certain contiguous columns and certain
continguous rows, say "C:E" and "5:10" for example, in two different
variables as string addresses.

I need to return the address of the intersection of these two ranges:
C5:E10
Is it possible to do this without parsing the range addresses and without
actually changing the user's current selection? If so, could someone
please
post a one or two line solution? I've been trying to use the "Intersect"
method without success.

Thanks much in advance.

Return intesected range
 

Bernie,

If I manually selected, say Rows 4 and 5 and, using the Control key, columnd
C and D, how would I trap the intersection of these two ranges (e.g. C4:D5) ?

Thanks in advance

Pete

"Bernie Deitrick" wrote:

strAddress = Intersect(Range(Var1),Range(Var2)).Address(False,F alse)

HTH,
Bernie
MS Excel MVP


"XP" wrote in message
...
I am using Office 2003 on Windows XP.

My code dynamically returns certain contiguous columns and certain
continguous rows, say "C:E" and "5:10" for example, in two different
variables as string addresses.

I need to return the address of the intersection of these two ranges: C5:E10
Is it possible to do this without parsing the range addresses and without
actually changing the user's current selection? If so, could someone please
post a one or two line solution? I've been trying to use the "Intersect"
method without success.

Thanks much in advance.

Return intesected range
 

Peter,

Sub Intersect2()
Dim myR As Range

If Selection.Areas.Count < 2 Then
MsgBox "You must select two areas"
Exit Sub
End If

Set myR = Intersect(Selection.Areas(1), Selection.Areas(2))

MsgBox myR.Address(False, False)

End Sub

HTH,
Bernie
MS Excel MVP

"Peter Rooney" wrote in message
...
Bernie,

If I manually selected, say Rows 4 and 5 and, using the Control key,
columnd
C and D, how would I trap the intersection of these two ranges (e.g.
C4:D5) ?

Thanks in advance

Pete

"Bernie Deitrick" wrote:

strAddress = Intersect(Range(Var1),Range(Var2)).Address(False,F alse)

HTH,
Bernie
MS Excel MVP


"XP" wrote in message
...
I am using Office 2003 on Windows XP.

My code dynamically returns certain contiguous columns and certain
continguous rows, say "C:E" and "5:10" for example, in two different
variables as string addresses.

I need to return the address of the intersection of these two ranges:
C5:E10
Is it possible to do this without parsing the range addresses and
without
actually changing the user's current selection? If so, could someone
please
post a one or two line solution? I've been trying to use the
"Intersect"
method without success.

Thanks much in advance.

Return intesected range
 

Bernie,

Just the job.

Thanks a lot! :-)

Pete



"Bernie Deitrick" wrote:

Peter,

Sub Intersect2()
Dim myR As Range

If Selection.Areas.Count < 2 Then
MsgBox "You must select two areas"
Exit Sub
End If

Set myR = Intersect(Selection.Areas(1), Selection.Areas(2))

MsgBox myR.Address(False, False)

End Sub

HTH,
Bernie
MS Excel MVP

"Peter Rooney" wrote in message
...
Bernie,

If I manually selected, say Rows 4 and 5 and, using the Control key,
columnd
C and D, how would I trap the intersection of these two ranges (e.g.
C4:D5) ?

Thanks in advance

Pete

"Bernie Deitrick" wrote:

strAddress = Intersect(Range(Var1),Range(Var2)).Address(False,F alse)

HTH,
Bernie
MS Excel MVP


"XP" wrote in message
...
I am using Office 2003 on Windows XP.

My code dynamically returns certain contiguous columns and certain
continguous rows, say "C:E" and "5:10" for example, in two different
variables as string addresses.

I need to return the address of the intersection of these two ranges:
C5:E10
Is it possible to do this without parsing the range addresses and
without
actually changing the user's current selection? If so, could someone
please
post a one or two line solution? I've been trying to use the
"Intersect"
method without success.

Thanks much in advance.