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