VB Code to select multiple named ranges
Prep for a pre-employment Excel test with Job Test Prep
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: VB Code to select multiple named ranges

  1. #1
    New Member
    Join Date
    Apr 2002
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    I have a spreadsheet with up to 30 named ranges. I want to select, set the print area and preview only those ranges that have data in a specific cell. I need this as a total report and not the ranges one at a time.

    I hope somebody can help.

    Liz

  2. #2
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Liz,

    Unless you write to another "Report" sheet, you probably won't be able to build a report of discontinuous ranges.

    One workaround -- Copy the ranges as pictures and paste into a report.

    Select the range. Hold down the shift key and pull down the Edit menu -- select Copy Picture. Do this for the rest of the named ranges and create your report.

    You are copying screen shots here, so if the data changes, the picture will not update.

    HTH,
    Jay

  3. #3
    New Member
    Join Date
    Apr 2002
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks,

    I can set the print area for discontinuous ranges by selecting the first named range from the name box and then holding down the Ctrl Key selecting the next name range that is required. I really wanted to automate this procedure as it is prone to user error.

    Liz

  4. #4
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Liz


    Not too sure if this could work in your situation, but have a look into "Report Manager" and "Custom Views" in the Excel Help. These 2 features work hand-in-hand and are ideal for Printing reports.




  5. #5
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    Hi Liz,

    In addition to Dave's suggestions, you can select your ranges in code.

    I named two discontinuous ranges, First and Second, and then selected them with

    Range("First, Second").Select

    I haven't yet solved reading them into an array and selecting the array, but this may suffice. The 30 ranges will make for a long statement, but will enable you to eliminate user error.

    Possibly a loop with the Choose function...hmmm...

    Bye,
    Jay

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com