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

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

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

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

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

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

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

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