VB Code to select multiple named ranges

[HSA]

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

 

[Jay Petrulis]

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

 

[HSA]

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

 

[Dave Hawley]

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.

 

[Jay Petrulis]

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