Selecting Range between two named ranges

  • Thread starter Thread starter Simon
  • Start date Start date
S

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
 
dim rng as range

with application
set rng = range(.names("Lower").referstorange, .names("Upper").referstorange)
end with
 
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
 
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...
 
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"))
 
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...
 
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
 
Back
Top