Selecting Range between two named ranges

[Simon]

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

 

[Jim Thomlinson]

dim rng as range

with application
set rng = range(.names("Lower").referstorange, .names("Upper").referstorange)
end with

 

[Bob Umlas]

set rng=range(range("lower"),range("Upper"))
Bob Umlas
Excel MVP

 

[Jacob Skaria]

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

 

[Jim Thomlinson]

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.range("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...

 

[Rick Rothstein]

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"))

 

[Jim Thomlinson]

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...

 

[Jim Thomlinson]

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