VB Code to select multiple named ranges

HSA · Apr 8th, 2002, 05:33 PM

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 · Apr 8th, 2002, 08:30 PM

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 · Apr 8th, 2002, 08:37 PM

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 · Apr 8th, 2002, 08:57 PM

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 · Apr 8th, 2002, 09:06 PM

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

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Apr 8th, 2002, 05:33 PM	#1
HSA New Member Join Date: Apr 2002 Posts: 8	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

Apr 8th, 2002, 08:30 PM	#2
Jay Petrulis MrExcel MVP Join Date: Mar 2002 Location: Chicago, IL USA Posts: 2,042	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

Apr 8th, 2002, 08:37 PM	#3
HSA New Member Join Date: Apr 2002 Posts: 8	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

Apr 8th, 2002, 08:57 PM	#4
Dave Hawley Banned Join Date: Feb 2002 Posts: 1,582	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.

Apr 8th, 2002, 09:06 PM	#5
Jay Petrulis MrExcel MVP Join Date: Mar 2002 Location: Chicago, IL USA Posts: 2,042	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