Excel 2002 - Challenge to Excel Experts - Bet You Can't... 2

[DaleWatson123321]

...Print 2 separate ranges on 1 page. Excel's competition (Lotus 123) can !!! - so why not Excel ??? In fact, printing 2 separate pages on 1 page is something that many spreadsheet users often want to do, and have been able to with Lotus 123 for SEVERAL years, so WHY NOT EXCEL ??? With Lotus 123, you can print MULTIPLE separate ranges on 1 page.

So come on Excel Experts, "show me your stuff" !!! Convince me that Excel has this capability.

Background Info: If you attempt to print 2 separate ranges on 1 page, Excel forces a page-break between the 2 ranges. This seems like an extremely easy bug to fix, but for some very strange reason, Microsoft has refused to address this very, very, very longstanding issue.

Excel Experts, if you find that there is NO SOLUTION to this problem, perhaps one of you has "SOME PULL" with Microsoft, and can reach someone in authority at Microsoft who can finally eliminate this BUG.

Your help would be greatly appreciated !!! Many Thanks. ...Dale Watson

If you like, I can be emailed, at: nd.watson@home.com, or dwatson@bsi.gov.mb.ca

 

[JVFriederick]

Sorry I can't help you. I just use the software, I don't write it.

(A Lotus user since 1983 myself)

 

[Dreamboat]

I'll call Bill in the morning. LOL
techsupportgirl@home.com

http://www.PiersonTech.com

Brainbench MVP for Microsoft Word

 

[DarkSun]

Dale,
a few of questions.

Once you have selected the ranges how would like them printed? eg. layout on screen.
do you want each range to be justified to the left?
do you want them printed as they appear on the screen with gaps?

if the second range is to the left of the first do you want them printed next to each other or below one another?

if you want ranges to appear after each other do you want them printed in the order they were selected?

There are probably more questions but these will do for now.
How does 123 deal with these?

DarkSun.

 

[snyderj]

The only way I know to do this in Excel is to use a filter. Instead of selecting ranges, have a column to indicate whether the row is included or not included. With the filter turned on and the print range set for "all" then only the filtered stuff will print.

Not a perfect solution, but workable. If this is not satisfactory you can follow these instructions from Microsoft:

http://support.microsoft.com/support/kb/articles/Q114/4/91.asp

 

[Dreamboat]

Oh, and hey! I don't know how YOU can complain! You're the one causing all the errors, DR. WATSON!! LOL
techsupportgirl@home.com

http://www.PiersonTech.com

Brainbench MVP for Microsoft Word

 

[DarkSun]

I would be quite happy to attempt to write an add-in for Excel.

Dale,
Let me know the answers to my questions and I will give it a go.

 

[DaleWatson123321]

===========
To DarkSun,
===========

Thanks for taking up the challenge !!! Here are the answers to your questions: 1) Each range left-justified ? - Yes. 2) Printed as they appear on screen ? - Yes - with NO Gaps. (If gaps are required, users would include blank rows or headings within each range). 3) If 2nd range is to left of 1st, print next to each other or below ? - Below. 4) Print order ? - Print in order they are entered by the user when printing. (For the record, Excel does permit users to select multiple ranges and assign ONE name to these ranges, and to use that one name when specifying the print range. However, as mentioned, it forces a page-break between each range, thus making it impossible to print these ranges on ONE page.

You also asked how 123 deals with this. It simply allows the user to enter the ranges (either as cell coordinates, or by using range names), with each coordinate or range name separated with a semi-colon. The ranges can be anywhere ... i.e. on one sheet or on multiple sheets. Bottom line (with Lotus 123) is that it simply DOES NOT FORCE A PAGE-BREAK like Excel. Lotus 123 leaves that decision up to the USER, who can easily insert a forced page-break if desired.

I hope this further explanation helps you understand the problem. Your offer to attempt to write an add-in for Excel is appreciated, and would be welcomed by Excel users, I am sure. However, If someone out there can "perform a miracle" by getting through to Microsoft and getting them to eliminate "that little piece of code" which forces the page-break (possibly no more difficult for them than changing a "1" to a "0&quot, then THAT would be a GREAT accomplishment !!!

Thanks again, DarkSun ...Dale Watson
( dwatson@bsi.gov.mb.ca -or- nd.watson@home.com )

===========
To snyderj,
===========

Thanks as well for your contribution !!! Your suggestion of using the "filter" option is welcomed, but I was well aware of this feature and haved used it in situations where it can be effective. (I have also set up applications with databases within Excel where data is extracted to separate sheets and printed from there.)

However, as you will probably appreciate from my above response (to DarkSun), the ranges are ones which are located in different columns and/or on different sheets. I am basically referring to different matrixes which have to be positioned in these separate areas because of the nature of the data ...i.e. column widths, and relation to other data which forms part of a much larger application than just one page. As mentioned, however, it is often the case that a user wants to be able to print (some of) these separate ranges on ONE page.

Thanks again, snyderj ...Dale Watson
( dwatson@bsi.gov.mb.ca -or- nd.watson@home.com )

 

[mala]

Hello friends,
I joined the forum today.
A simple way to print non-contiguous ranges is to (either manually or programmatically) copy pictures of the ranges to be printed next to each other on a blank sheet and print the range containing the pictures.
If the configuration of the printing of the non-contiguous ranges is unchanging, you can also used linked pictures so that you don't have to do the copy-paste picture process over and over again.
This process also,to some extent. takes care of different column widths in different zones as pointed out in the msg above as it is possible to resize the individual pictures to fit into the matrix of pictures in the range actually printed.

 

[DaleWatson123321]

To Mala, and other Excel Experts.

Thanks for your contribution. I'm sure you mean well, but I respectfully suggest that your solution is not realistic. I have attempted to do as you suggest but have failed to get the desired results. Perhaps either I am dumb or Excel is NOT the user-friendly product some make it out to be.

If you could provide more specific instructions which others in the Excel world could utilize, this would be most appreciated.

May I suggest, however, that all of us need to accept the fact that Microsoft has simply FAILED MISERABLY in this one area, and they need to be made to realize this fact, and to finally correct this SEVERE limitation. To this end, I have recently lodged (another) formal request to Microsoft in the hope that they will finally fix this BUG.

Bottom line, is that the task of printing multiple ranges on ONE page, should be no more difficult than it has been with Lotus 123 for SEVERAL years - specifically entering the range names, separated with a semi-colon. This has been very clean and SIMPLE. No muss, no fuss, with cutting and pasting pictures!!!

Please be bold and "put me in my place" if you believe I am "off base" with my comments. Otherwise, I would appreciate everyone "getting on board" and complaining to Microsoft, to get this little BUG (but very frustrating BUG) fixed once and for all.

A final point... If you haven't yet experienced the flexibility of Lotus 123, please visualize for a moment the ease with which one can create complex one-page forms which pull data from multiple separate sources - simply by referencing their range names. All Microsoft has to do to duplicate this feature, is to REMOVE that ONE piece of code which FORCES a PAGE-BREAK between the separate ranges.

"H E L L O M I C R O S O F T - Hope you are listening !!!"

...Dale Watson
dwatson@bsi.gov.mb.ca -or- nd.watson@home.com

 

[Greyhound1]

My solution would be to first use a macro to record a copy & paste to a dummy sheet. Print the dummy sheet. Then programatically delete the dummy sheet when the procedure is completed. (With application.screenupdating=FALSE and Application.displayalerts=FALSE) After the macro was recorded, I would clean up the unneccessary codelines and set the display features to TRUE. Maybe I'm oversimplifying your challenge, but I am already using my method to print two print areas on one sheet in Excel.

 

[DaleWatson123321]

Greyhound1,

Thanks for your solution. Awhile ago, however, I happened upon an EXCELLENT solution, and have posted it as an "FAQ".

The FAQ # is 68-1161. To access the FAQ's, just click on the "FAQs" tab on the top of the Tek-Tips page.

This FAQ is the first one listed under "Best of Excel".

I expect you'll find this solution EXTREMELY useful, but
please advise as to how you make out.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[iSeriesCodePoet]

<SNIP> (possibly no more difficult for them than changing a &quot;1&quot; to a &quot;0&quot </SNIP>

Um... this is Microsoft code we are talking about. There are probably 1000 lines of code to deal with that. LOL Mike Wills
AS400 Programmer

 

[DaleWatson123321]

Mike,

Your humour is possibly more accurate than my suggestion.

A further update to this item... In addition to the FAQ I submitted, I first posted this &quot;hidden feature&quot; on October 12th under heading &quot;EXCEL HIDDEN FEATURE: Printing Disjointed Ranges on ONE Page.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[JrClown]

YAWN!

&quot;The reward of one duty done is the power to fulfill another&quot;
<%
Jr_Clown@Yahoo.com
%>
​

 

[dsi]

Dale:

You can include a direct link to the FAQ by keeping the string together: FAQ # is 68-1161 -> faq68-1161

 

[DaleWatson123321]

dsi,

Thanks !!! ...will be sure to do so in future...

...Dale

 

[Greyhound1]

Thanks, Dale will do....