Fit Printout onto 1 Page (Excel) 4

[nickjar2]

I had a problem last week trying to get a couple of ranges added together and printed out. Someone called Goska helped me oput but now I am stuck again. Here is the code I now have:
Range("A3:O" & iRows & ", A86:O101&quot.Select
With Worksheets("Pressure Points&quot.PageSetup
.Zoom = False
.FitToPagesTall = 1
.FitToPagesWide = 1
End With

But A3:O & iRows prints on 1st page and A86:O101 prints on 2nd page. Is there any way around this? I really need it to print on 1 page.

Cheers,

Nick

 

[DaleWatson123321]

Hi nickjar2,

The only apparant workaround for your situation is to hide the rows between your two ranges, using code such as:
Rows("19:84&quot.Select
Selection.EntireRow.Hidden = True

Fortunately in your situation, your ranges are above/below each other. In other situations where the ranges are "disjointed" - i.e. in totally separate columns, or on separate sheets, it becomes IMPOSSIBLE to include the two ranges on ONE page. As you have experienced, Excel "FORCES" a Page-Break between the two ranges.

This has been a LONG-STANDING BUG with Excel. I have attempted SEVERAL times to get Microsoft to fix this BUG once and for all, but to no avail. Interestingly, the competition, Lotus 123 has NEVER had this problem or restriction. And, the solution for Microsoft is probably no more difficult than changing a "1" to a "0", or eliminating the line of code which forces the page break.

This forced page break should logically NEVER have been included in the first place, as it offers NO benefit. It makes perfect sense to LEAVE IT UP TO THE USER as to whether a "forced page break" is required, and the option has always been there to insert a forced page break if required.

Perhaps what Microsoft requires is for readers of this forum to "FORCE THE ISSUE" with Microsoft - by forwarding a copy of this item to Microsoft, or whatever other option readers might suggest.

Bottom line on this is, this BUG has been a "thorn" in the side of MANY Excel users for FAR TOO LONG, and needs to be KILLED. So, readers, PLEASE HELP OUT, and Microsoft, PLEASE "LISTEN TO YOUR USERS".

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

 

[JVFriederick]

Do a switcharoo.

Set area to print as the entire possible range. Then use macro the hide the undesired rows as Dale suggested.

 

[nickjar2]

Cheers Dale and JV.
When I use:
Selection.EntireRow.Hidden = True

I get error 1004 - Unable to set the hidden property of the range class.

God I hate excel! We are doing conversion from Lotus to Excel and there are so many things that are so easy to do in Lotus but not easy in Excel.

Thanks in advance.

Nick

 

[nickjar2]

I have just worked out that I will need to unprotect the sheet first.
Once it is working i will come back and give the both of you stars.

Thanks again,

Nick

 

[nickjar2]

All done. Cheers lads.

I love this site. I can try and help people with Access, and people can help me with Excel. Brilliant! And I usually get a quick response.

And I agree with you Dale about Microsoft. I have found problems in Access which were not problems in Approach.

Anyway,

Nice one.

Nick

 

[JVFriederick]

Nickjar2 - I understand your frustrations at times with Excel. I was a 15 year Lotus user before I ever touched Excel. There are still features I sorely miss.

The best advice I can give you it to not try and "convert" your files, layout design, or even thinking from Lotus to Excel. It's like fighting a stream current, and frustrating.

I've found it's better to put on Excel glasses and then look at your tasks. How can you take advantage of the features? Take for example the filter within Excel. Instead of extracting records to another area as you may have done in Lotus, simply leave them where they are and use the filter to hide what you don't need.

I'm not trying to preach here, just trying to share my thoughts. At one time I was a very frustrated puppy dragged kicking and screaming into using Excel.