Printing problem

[Guest]

I've set up a sheet to fit to one page. Using the print area I have defined,
only half the height of the page is used. I would expect therefore the
entire width of the page to be used in order to zoom out as little as
posible. When I print (preview) a large margin is left on the right hand
side of my page. I've tried setting the margins to zero, but to no avail.
Any suggestions?
Thanks
Rob

 

[paul.robinson]

Hi Rob
See if this is any use to you. It works on the used range of the active
sheet, so you can follow it with
ActiveSheet.PrintOut

'Simplified version of a function written by Francois Van Wauwe,
'Microsoft.Public.Excel.Programming, 25th Sept 1998

Function Set_Used_Print_Area()
Dim MyZoom As Integer
Dim UsedWidth As Variant, UsedHeight As Variant
Dim IsLandScape As Boolean
Dim pw As Double, ph As Double
Dim ZoomWidth As Integer, ZoomHeight As Integer
With ActiveSheet.UsedRange
UsedWidth = .Width
UsedHeight = .Height
End With
If UsedWidth > UsedHeight Then
' Landscape
IsLandScape = True
pw = Application.CentimetersToPoints(24.7)
ph = Application.CentimetersToPoints(16.6)
With ActiveSheet.PageSetup
.TopMargin = Application.CentimetersToPoints(1)
.BottomMargin = Application.CentimetersToPoints(1)
.LeftMargin = Application.CentimetersToPoints(1.5)
.RightMargin = Application.CentimetersToPoints(1.5)
End With
Else
' Portrait
IsLandScape = False
pw = Application.CentimetersToPoints(16.6)
ph = Application.CentimetersToPoints(24.7)
With ActiveSheet.PageSetup
.LeftMargin = Application.CentimetersToPoints(1)
.RightMargin = Application.CentimetersToPoints(1)
.TopMargin = Application.CentimetersToPoints(1.5)
.BottomMargin = Application.CentimetersToPoints(1.5)
End With
End If

ZoomWidth = Int(pw / UsedWidth * 100)
ZoomHeight = Int(ph / UsedHeight * 100)
If ZoomWidth < ZoomHeight Then 'smallest of the two
MyZoom = ZoomWidth
Else
MyZoom = ZoomHeight
End If
With Application.ActiveSheet.PageSetup
If (MyZoom <= 400) And (MyZoom >= 10) Then
.Zoom = MyZoom
Else
.Zoom = 100
End If
If IsLandScape Then
.Orientation = xlLandscape
Else
.Orientation = xlPortrait
End If
End With
End Function

regards
Paul