Use code to define a range (2002, SP2)

Topic

New Reply

This seems like it should be easy, but I have not been able to figure out how to do it ….(Since they moved everything to Visual Basic, the help system seems so useless to help you figure out something like this. But that’s another story …)

I have a series of 10 named ranges, 5 on top of 5. Like this:

R1 R2 R3 R4 R5
R6 R7 R8 R9 R10

I want to use code to define a new range (“AllData”) that encompasses all 10 of the named ranges. I cannot use specific cell references, because the size of the named ranges changes each time I run the query that produces the data in the worksheet. The query that produces the worksheet gives (my pre-defined) names to each of the ranges. Effectively, I want to set a new named range (“AllData”) with upper left boundary equal to the first cell of range R1 and a lower right boundary equal to the last cell of the R10 range.

Any idea how I can do that?

Reply | Quote

Replies

Set AllData = Union(R1, R2, R3, R4, R5, R6, R7, R8, R9, R10)

Reply | Quote

I had stumbled across Application.Union(R1,R2…) in Help, but I completely missed the “…” in the example, to indicate you could string together more than just two ranges.

Making a new range of R1 and R2, then another statement to expand that new range to include R3, then R4, etc., seemed like such an uncouth way to do it Thanks for the eye-opener.

Reply | Quote

The hidden question within the question ….

Is there no way to extract the address of the first and last cell in a named range? Because even the Union(R1, R2, R3, etc.) seems ungainly—having to list each range like that. If you define a range like this R1 = Range(A1:A5) … it seems like you should later be able to extract those cell address from the named range. So that you could do something like this:

AllData = Range( R1(RangeR1StartAddress) : R10(RangeR10EndAddress) )

Reply | Quote

Does this do what you want?

Dim oFirst As Range
Dim oLast As Range
Dim AllData As Range
Set oFirst = R1.Cells(1, 1)
Set oLast = R10.Cells(R10.Rows.Count, R10.Columns.Count)
Set AllData = Range(oFirst, oLast)

Reply | Quote

Yes, exactly. The result is the same as using Union(R1,R2,…). Thank you.

And this is an excellent example of my frustration with the new Help systems in Office/VB. How am I supposed to know that you can do that? That a “range” can take “.cell()” on the end of it to refer to cells within that range? I spent, literally, hours going back and forth and throughout both VB help and Excel help to try to get it to tell me how to do that. It used to be (two versions of Office ago?), knowing little about what I was doing (bet you couldn’t tell :-), I could rely Help, which seemed so much more intuitive then, to help me pound out an application. No longer. For example, if I search for “ranges” the first thing that comes back is something about “SecondValue Property” and a bunch of other (mostly equally useless) stuff (as it relates to what I need to do). Search on “cells” gets me more stuff that gets me close, but never actually gets me there. If I type in “range.cells” or “referring to cells within a range” that gets me there, but who can think to type in exactly that. Very frustrating. I can’t believe I’m the only one experiencing this frustration.

Did they do this just to encourage people to hire programmers??

It doesn’t seem like a course in VB would be the answer, because that’s not going to tell me Range takes .Cell() either. What am I missing?

Reply | Quote

I think the best thing to do is to study code examples provided by others, for example (there are many more):
The Microsoft Knowledge Base
Chip Pearson‘s site
John Walkenbach‘s site
and last, but not least of course, this forum

Reply | Quote

I also suggest “playing” with any code (step thru it, make changes, tweak it). There are multiple ways to do anything in excel. Some are more efficient than others, but many are equivalent (or nearly equivalent) that the best thing to do is work with code and techniques that you understand.

Just because I do something one way, you could do it in an entirely different way: neither is wrong, both may be correct if they yield the same answer.

You don’t have to “master” all the techniques, sometimes it is best to master a few and then struggle thru with the rest. Find some good techniques and use them to death, if you need others expand your “toolbox”.

Personally, I learn a lot of tricks, by answering the questions here and in dailyexceltips or working thru the code that others produce. Many of the questions involve things I have never or rarely used, so it allows me to find an example to make it work.

Steve

Reply | Quote

I also suggest “playing” with any code (step thru it, make changes, tweak it). There are multiple ways to do anything in excel. Some are more efficient than others, but many are equivalent (or nearly equivalent) that the best thing to do is work with code and techniques that you understand.

Just because I do something one way, you could do it in an entirely different way: neither is wrong, both may be correct if they yield the same answer.

You don’t have to “master” all the techniques, sometimes it is best to master a few and then struggle thru with the rest. Find some good techniques and use them to death, if you need others expand your “toolbox”.

Personally, I learn a lot of tricks, by answering the questions here and in dailyexceltips or working thru the code that others produce. Many of the questions involve things I have never or rarely used, so it allows me to find an example to make it work.

Steve

Reply | Quote

I think the best thing to do is to study code examples provided by others, for example (there are many more):
The Microsoft Knowledge Base
Chip Pearson‘s site
John Walkenbach‘s site
and last, but not least of course, this forum

Reply | Quote

A couple of random comments FWTAW:

I also don’t like the HTML based Help Engine, I agree that frequently it doesn’t take you directly where you want to look.

If you describe your problem more generally (not -too-generally ) someone here might be able to suggest a better approach. For example, maybe:

— you could name the entire range AllData so that it flexes with the amount of data
— you could see if CurrentRegion or UsedRange works in your scenario
— you could see if SpecialCells(type, args) has any application
— or some odd combination such as Intersect(CurrentRegion, Specialcells()) might apply

Posting a censored (no proprietary information) workbook as an attachment is always helpful in showing fellow Loungers what you are trying to accomplish.

Reply | Quote

A couple of random comments FWTAW:

I also don’t like the HTML based Help Engine, I agree that frequently it doesn’t take you directly where you want to look.

If you describe your problem more generally (not -too-generally ) someone here might be able to suggest a better approach. For example, maybe:

— you could name the entire range AllData so that it flexes with the amount of data
— you could see if CurrentRegion or UsedRange works in your scenario
— you could see if SpecialCells(type, args) has any application
— or some odd combination such as Intersect(CurrentRegion, Specialcells()) might apply

Posting a censored (no proprietary information) workbook as an attachment is always helpful in showing fellow Loungers what you are trying to accomplish.

Reply | Quote

Yes, exactly. The result is the same as using Union(R1,R2,…). Thank you.

And this is an excellent example of my frustration with the new Help systems in Office/VB. How am I supposed to know that you can do that? That a “range” can take “.cell()” on the end of it to refer to cells within that range? I spent, literally, hours going back and forth and throughout both VB help and Excel help to try to get it to tell me how to do that. It used to be (two versions of Office ago?), knowing little about what I was doing (bet you couldn’t tell :-), I could rely Help, which seemed so much more intuitive then, to help me pound out an application. No longer. For example, if I search for “ranges” the first thing that comes back is something about “SecondValue Property” and a bunch of other (mostly equally useless) stuff (as it relates to what I need to do). Search on “cells” gets me more stuff that gets me close, but never actually gets me there. If I type in “range.cells” or “referring to cells within a range” that gets me there, but who can think to type in exactly that. Very frustrating. I can’t believe I’m the only one experiencing this frustration.

Did they do this just to encourage people to hire programmers??

It doesn’t seem like a course in VB would be the answer, because that’s not going to tell me Range takes .Cell() either. What am I missing?

Reply | Quote

Does this do what you want?

Dim oFirst As Range
Dim oLast As Range
Dim AllData As Range
Set oFirst = R1.Cells(1, 1)
Set oLast = R10.Cells(R10.Rows.Count, R10.Columns.Count)
Set AllData = Range(oFirst, oLast)

Reply | Quote

Two things.

I think I made an incorrect assumption, namely that you had already gone through these steps (assuming Dims are already set):

Set R1 = Range(“R1”)
Set R2 = Range(“R2”)
Set R3 = Range(“R3”)

… etc. You will need to do that or you can do it this way:

Set Alldata = Union(Range(“R1”), Range(“R2”), Range(“R3”),…)

2. If the ranges all adjoin to be one contigous range, then you can do easily it your way or HanV’s way, but Union will handle discontiguous ranges, to create the equivalent of a Range such as =A1:C5,A10:B15,,A20:C25,E1:G5,E10:G15,E20:G25

But Union() is the most convenient way.

Reply | Quote

R1 through R10 are invalid as range names, since they are cell references. Range(“R1”) refers to the cell in column R, row 1. You can’t define R1 as a name. R1 etc. are perfectly acceptable as names for Range objects in VBA, though.

Reply | Quote

True, Hans, though I took the names to be hypothetical.

Reply | Quote

OK, you can consider my comments as hypothetical too

Reply | Quote

OK, you can consider my comments as hypothetical too

Reply | Quote

True, Hans, though I took the names to be hypothetical.

Reply | Quote

Two things.

I think I made an incorrect assumption, namely that you had already gone through these steps (assuming Dims are already set):

Set R1 = Range(“R1”)
Set R2 = Range(“R2”)
Set R3 = Range(“R3”)

… etc. You will need to do that or you can do it this way:

Set Alldata = Union(Range(“R1”), Range(“R2”), Range(“R3”),…)

2. If the ranges all adjoin to be one contigous range, then you can do easily it your way or HanV’s way, but Union will handle discontiguous ranges, to create the equivalent of a Range such as =A1:C5,A10:B15,,A20:C25,E1:G5,E10:G15,E20:G25

But Union() is the most convenient way.

Reply | Quote

The hidden question within the question ….

Is there no way to extract the address of the first and last cell in a named range? Because even the Union(R1, R2, R3, etc.) seems ungainly—having to list each range like that. If you define a range like this R1 = Range(A1:A5) … it seems like you should later be able to extract those cell address from the named range. So that you could do something like this:

AllData = Range( R1(RangeR1StartAddress) : R10(RangeR10EndAddress) )

Reply | Quote

Set AllData = Union(R1, R2, R3, R4, R5, R6, R7, R8, R9, R10)

Reply | Quote

Just because I’m curious:

What are the real names of those ranges? Are they truly R1 … R10?
I would be surprised, since Excel’s normal user interface would disallow you entering a name like that.

And a small tip: Have you tried my Name Manager (see the Excel MVP Page below)?

Reply | Quote

I’m using Showcase Query to get the data from the AS400 and drop it into Excel. Showcase Query is using ranges actually named “LinkedArea1” … “LinkedArea10”.

It looks like that NameManager might be a useful tool. I’m a little hesitant to try it in this instance though, because I’m afraid it might mess-up the links to Showcase Query (which are tiresome to build, since I’m combining two different datasets on the AS400 into one dataset in Excel).

Reply | Quote

[indent]

---

It looks like that NameManager might be a useful tool. I’m a little hesitant to try it in this instance though, because I’m afraid it might mess-up the links to Showcase Query

---

[/indent]
Don’t be. The Name Manager is safe, since it does not look at queries at all, it just helps you work with defined names.

Reply | Quote

[indent]

---

It looks like that NameManager might be a useful tool. I’m a little hesitant to try it in this instance though, because I’m afraid it might mess-up the links to Showcase Query

---

[/indent]
Don’t be. The Name Manager is safe, since it does not look at queries at all, it just helps you work with defined names.

Reply | Quote

I’m using Showcase Query to get the data from the AS400 and drop it into Excel. Showcase Query is using ranges actually named “LinkedArea1” … “LinkedArea10”.

It looks like that NameManager might be a useful tool. I’m a little hesitant to try it in this instance though, because I’m afraid it might mess-up the links to Showcase Query (which are tiresome to build, since I’m combining two different datasets on the AS400 into one dataset in Excel).

Reply | Quote

Just because I’m curious:

What are the real names of those ranges? Are they truly R1 … R10?
I would be surprised, since Excel’s normal user interface would disallow you entering a name like that.

And a small tip: Have you tried my Name Manager (see the Excel MVP Page below)?

Reply | Quote