Working with a range within a range ?

[Guest]

I am trying to assing a sub range within a larger range of cells on a
worksheet.
I would like to be able to assign this smaller sub range within the larger
range of cells on my worksheet by just designating wich column within the
larger range should be used for the smaller range and the rest of the
paramiters such as number of rows for the smaller range should be taken from
the range paramiters of the larger range.

Set LRange = Worksheets("Sheet1").Range("A110")

Set SRange = Range(LRange.Columns(1)) <-- This works but only gives me a
range of "A1" I need it to be "A1:A10"

anyone ????

Thanks again
Dan Thompson

 

[Bob Phillips]

Set LRange = Worksheets("Sheet1").Range("A110")

Set SRange = LRange.Columns(1)

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Guest]

You can use the intersect function which gives you the intersection of two
ranges.

Sub SubRange()
Dim rngLarge As Range
Dim rngSmall As Range

Set rngLarge = Sheet1.Range("A110")

Set rngSmall = Intersect(Sheet1.Range("A1").EntireColumn, rngLarge)
rngSmall.Select

End Sub

You can modify this as is necessary. I made some specific references.

HTH

 

[Guest]

Thanks Jim your solution worked for what I need to do.

Thanks for your input too Bob. However usning your method does sort of work
but it only gives you a row count of 1 for the new sub range. This is the same
problem I was already having. Thanks anyway

 

[Myrna Larson]

For me, the following displays A110 and C1:C10

Sub Test()
Dim Rng1 As Range
Dim Rng2 As Range
Set Rng1 = Range("A110")
Set Rng2 = Rng1.Columns(3)
Debug.Print Rng1.Address, Rng2.Address
End Sub

OTOH, this statement produces an error.

Debug.Print Range(Rng1.Columns(3)).Address

probably because Range wants 2 references rather than just 1.