Page Setup Not Fitting To One Page Wide

[MDW]

I've written some code that allows users to change the print area on a
worksheet without ever having to actually play with the page setup options.
The actual setting of the print area works fine; however, we always always
want it to fit to one page wide, taking up the maximum width, and having the
number of pages tall vary depending on need.

What seems to happen is that sometimes, even though I believe I'm setting
everything properly, there's still a very generous amount of horizontal space
not being used. The margins are a constant .25". If I fiddle around with the
% zoom, I can get it to take up the entire width like I want. Any ideas what
could be going on?

The code is below:

With shtMain.PageSetup

.Zoom = False
.PaperSize = xlPaperLegal
.Orientation = xlLandscape

.FitToPagesTall = 300 ' Arbitrarily large number to ensure we have
enough pages tall - usually the number of pages neeed is betweem 2 and 7
.FitToPagesWide = 1 ' ALWAYS want 1 page wide

.PrintArea = objView.Address ' This works fine

.CenterHeader = "&""Times New Roman""&09&B" & strHeader

End With

 

[CBrine]

This code should accomplish what you are looking for. It will set the print
to optimum lenght based on 1 page wide.

ws.PageSetup.Zoom = False
ws.PageSetup.FitToPagesTall = False
ws.PageSetup.FitToPagesWide = 1

HTH
Cal

 

[CBrine]

WS. is a reference to a worksheet object

dim ws as worksheet
set ws = activesheet
ws.PageSetup.Zoom = False
ws.PageSetup.FitToPagesTall = False
ws.PageSetup.FitToPagesWide = 1

 

[Dave Peterson]

First, you should be able to just use
..fittopagestall = false
instead of fiddling with any number.

My second guess is that you may be printing empty columns. Maybe objview
includes columns that are empty???