Hi Frank
This works reasonably well for me. Overdoes things a bit for small
tables. Operates on the UsedRange of the ActiveSheet.

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

Function Set_My_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
"Frank_Hamersley" wrote in message ...
I am trying to discover a way to optimise the .PageSetup properties of a
sheet (in VBA) so the user can simply click PrintPreview or Print... without
needing to adjust any properties to make the printed report readable.

The sheet concerned can have a variable amount of data and I want to
implement the following rules. Starting with .Orientation=xlLandscape...
a) if it fits on a single page leave it as is,
b) if the page appears to be narrow and long switch to xlPortrait to
maximise use of the page,
c) set a .Zoom % to achieve a 1 page wide print job (and 1 or more pages
down) so long as the .Zoom percentage does not go below (say) 50% (ie. avoid
an unreadable "greeked" result).

Has anyone been here before (in Excel 2K)? NB the .UsedRange property is
viable on this sheet!

Cheers,
Frank.