Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Lounger
    Join Date
    Mar 2001
    Location
    Tampa, Florida, USA
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Use code to define a range (2002, SP2)

    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?

  2. #2
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Use code to define a range (2002, SP2)

    Set AllData = Union(R1, R2, R3, R4, R5, R6, R7, R8, R9, R10)
    -John ... I float in liquid gardens
    UTC -7ąDS

  3. #3
    Lounger
    Join Date
    Mar 2001
    Location
    Tampa, Florida, USA
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Use code to define a range (2002, SP2)

    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.

  4. #4
    Lounger
    Join Date
    Mar 2001
    Location
    Tampa, Florida, USA
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Use code to define a range (2002, SP2)

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

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Use code to define a range (2002, SP2)

    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)

  6. #6
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Use code to define a range (2002, SP2)

    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.
    -John ... I float in liquid gardens
    UTC -7ąDS

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Use code to define a range (2002, SP2)

    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.

  8. #8
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Use code to define a range (2002, SP2)

    True, Hans, though I took the names to be hypothetical.
    -John ... I float in liquid gardens
    UTC -7ąDS

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Use code to define a range (2002, SP2)

    OK, you can consider my comments as hypothetical too <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

  10. #10
    Lounger
    Join Date
    Mar 2001
    Location
    Tampa, Florida, USA
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Use code to define a range (2002, SP2)

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

    <rant on>
    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?
    <rant off>

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Use code to define a range (2002, SP2)

    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 <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

  12. #12
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Use code to define a range (2002, SP2)

    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

  13. #13
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Use code to define a range (2002, SP2)

    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 <img src=/S/smile.gif border=0 alt=smile width=15 height=15>) 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.
    -John ... I float in liquid gardens
    UTC -7ąDS

  14. #14
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Use code to define a range (2002, SP2)

    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)?
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  15. #15
    Lounger
    Join Date
    Mar 2001
    Location
    Tampa, Florida, USA
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Use code to define a range (2002, SP2)

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

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •