Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selecting Range between two named ranges
Hi,
I have two named ranges, lets call them "Lower" and "Upper", I want to set a range object called Data to be the range of cells between "Lower" and "Upper", how can I do this, I keep getting error 400. Thanks. Simon |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selecting Range between two named ranges
dim rng as range
with application set rng = range(.names("Lower").referstorange, .names("Upper").referstorange) end with -- HTH... Jim Thomlinson "Simon" wrote: Hi, I have two named ranges, lets call them "Lower" and "Upper", I want to set a range object called Data to be the range of cells between "Lower" and "Upper", how can I do this, I keep getting error 400. Thanks. Simon |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selecting Range between two named ranges
set rng=range(range("lower"),range("Upper"))
Bob Umlas Excel MVP "Jim Thomlinson" wrote in message ... dim rng as range with application set rng = range(.names("Lower").referstorange, .names("Upper").referstorange) end with -- HTH... Jim Thomlinson "Simon" wrote: Hi, I have two named ranges, lets call them "Lower" and "Upper", I want to set a range object called Data to be the range of cells between "Lower" and "Upper", how can I do this, I keep getting error 400. Thanks. Simon |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selecting Range between two named ranges
Set rng = Range(Range("Lower"), Range("Upper"))
OR (do you mean between)..If you are referring to different rows within the same column Set rng = Range(Range("lower").Offset(1), Range("upper").Offset(-1)) If this post helps click Yes --------------- Jacob Skaria "Simon" wrote: Hi, I have two named ranges, lets call them "Lower" and "Upper", I want to set a range object called Data to be the range of cells between "Lower" and "Upper", how can I do this, I keep getting error 400. Thanks. Simon |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selecting Range between two named ranges
I believe the code you posted assumes that those 2 named ranges are on the
active sheet. Assuming this code is in a standard code module then the code you posted is equivalent to: set rng=range(activesheet.range("lower"),activesheet.r ange("Upper")) If the code is in a specific sheet then the default is that the named ranges exist in that sheet. Assuming that these are global named ranges the code I posted will always work (assuming that lower and upper are on the same sheet). If I have gone off the bean somewhere please post back. Most of my expertise has been the result of this forum and trial and error. More error than anything else... -- HTH... Jim Thomlinson "Bob Umlas" wrote: set rng=range(range("lower"),range("Upper")) Bob Umlas Excel MVP "Jim Thomlinson" wrote in message ... dim rng as range with application set rng = range(.names("Lower").referstorange, .names("Upper").referstorange) end with -- HTH... Jim Thomlinson "Simon" wrote: Hi, I have two named ranges, lets call them "Lower" and "Upper", I want to set a range object called Data to be the range of cells between "Lower" and "Upper", how can I do this, I keep getting error 400. Thanks. Simon |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selecting Range between two named ranges
And, of course, the order that Range("Lower") and Range("Upper") appear
within their encompassing Range call is immaterial; that is, this will work as well... Set rng = Range(Range("Upper"), Range("Lower")) -- Rick (MVP - Excel) "Bob Umlas" wrote in message ... set rng=range(range("lower"),range("Upper")) Bob Umlas Excel MVP "Jim Thomlinson" wrote in message ... dim rng as range with application set rng = range(.names("Lower").referstorange, .names("Upper").referstorange) end with -- HTH... Jim Thomlinson "Simon" wrote: Hi, I have two named ranges, lets call them "Lower" and "Upper", I want to set a range object called Data to be the range of cells between "Lower" and "Upper", how can I do this, I keep getting error 400. Thanks. Simon |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selecting Range between two named ranges
Sorry... Off the bean. That only applies if the code is in the worksheet.
Then it will error out if the named ranges are on a different worksheet. It works fine if it is in a standard code module. I have been using that way I posted all this time based on an assuption that just was not true... -- HTH... Jim Thomlinson "Jim Thomlinson" wrote: I believe the code you posted assumes that those 2 named ranges are on the active sheet. Assuming this code is in a standard code module then the code you posted is equivalent to: set rng=range(activesheet.range("lower"),activesheet.r ange("Upper")) If the code is in a specific sheet then the default is that the named ranges exist in that sheet. Assuming that these are global named ranges the code I posted will always work (assuming that lower and upper are on the same sheet). If I have gone off the bean somewhere please post back. Most of my expertise has been the result of this forum and trial and error. More error than anything else... -- HTH... Jim Thomlinson "Bob Umlas" wrote: set rng=range(range("lower"),range("Upper")) Bob Umlas Excel MVP "Jim Thomlinson" wrote in message ... dim rng as range with application set rng = range(.names("Lower").referstorange, .names("Upper").referstorange) end with -- HTH... Jim Thomlinson "Simon" wrote: Hi, I have two named ranges, lets call them "Lower" and "Upper", I want to set a range object called Data to be the range of cells between "Lower" and "Upper", how can I do this, I keep getting error 400. Thanks. Simon |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selecting Range between two named ranges
And on re-reading my initial post I accidentally used Application instead of
ThisWorkbook which absolutely will not work. I think I should just go and lie down now... Dim Data as Range with Thisworkbook set data = range(.names("Upper").referstorange, .names("Lower").referstorange) end with -- HTH... Jim Thomlinson "Jim Thomlinson" wrote: I believe the code you posted assumes that those 2 named ranges are on the active sheet. Assuming this code is in a standard code module then the code you posted is equivalent to: set rng=range(activesheet.range("lower"),activesheet.r ange("Upper")) If the code is in a specific sheet then the default is that the named ranges exist in that sheet. Assuming that these are global named ranges the code I posted will always work (assuming that lower and upper are on the same sheet). If I have gone off the bean somewhere please post back. Most of my expertise has been the result of this forum and trial and error. More error than anything else... -- HTH... Jim Thomlinson "Bob Umlas" wrote: set rng=range(range("lower"),range("Upper")) Bob Umlas Excel MVP "Jim Thomlinson" wrote in message ... dim rng as range with application set rng = range(.names("Lower").referstorange, .names("Upper").referstorange) end with -- HTH... Jim Thomlinson "Simon" wrote: Hi, I have two named ranges, lets call them "Lower" and "Upper", I want to set a range object called Data to be the range of cells between "Lower" and "Upper", how can I do this, I keep getting error 400. Thanks. Simon |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Selecting multiple named ranges | Excel Programming | |||
Selecting more than 1 named range | Excel Programming | |||
Named ranges scope / workbook/worksheet level named ranges- changeswith variable use... | Excel Programming | |||
Index and named ranges selecting difficulty | Excel Worksheet Functions | |||
Selecting Named Ranges | Excel Programming |