Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Working with a range within a range ?
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("A1:D10") 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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Working with a range within a range ?
Set LRange = Worksheets("Sheet1").Range("A1:D10")
Set SRange = LRange.Columns(1) -- HTH RP (remove nothere from the email address if mailing direct) "Dan Thompson" wrote in message ... 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("A1:D10") 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Working with a range within a range ?
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("A1:D10") 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 "Dan Thompson" wrote: 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("A1:D10") 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Working with a range within a range ?
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 "Dan Thompson" wrote: 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("A1:D10") 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Working with a range within a range ?
For me, the following displays A1:D10 and C1:C10
Sub Test() Dim Rng1 As Range Dim Rng2 As Range Set Rng1 = Range("A1:D10") 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. On Fri, 4 Mar 2005 14:13:02 -0800, Dan Thompson wrote: 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 "Dan Thompson" wrote: 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("A1:D10") 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Working with the range object | Excel Discussion (Misc queries) | |||
Working with a range | Excel Discussion (Misc queries) | |||
Working with a selected range | Excel Programming | |||
setting a range not working, please help | Excel Programming | |||
Range selection not working | Excel Programming |