Optimizing print areas

[DavidB]

Hi there

I like using the fit to page option within Excel, however, a report that a third party tool generates is sometimes better displayed in landscape rather than portrait and vice versa. I would like to be able to change this quickly / automatically. What i envisage is something like auto-fit for fonts within a cell.

Does anyone know of some VBA code to determine and change the page orientation to result in the larger area being occupied in a printed output? (This might be able to be determined by the zooming percentage??)

Thanks DavidB

 

[footoo]

Assuming you want to fit to one page :

Sub SetPageOrientation()
Dim ls%, pt%
With ActiveSheet.PageSetup
    .Zoom = False
    .FitToPagesWide = 1
    .FitToPagesTall = 1
    .Orientation = xlLandscape
    ExecuteExcel4Macro ("PAGE.SETUP(,,,,,,,,,,,,{#N/A,#N/A})")
    ls = .Zoom
    .Zoom = False
    .FitToPagesWide = 1
    .FitToPagesTall = 1
    .Orientation = xlPortrait
    ExecuteExcel4Macro ("PAGE.SETUP(,,,,,,,,,,,,{#N/A,#N/A})")
    pt = .Zoom
    If pt < ls Then
        .Zoom = ls
        .Orientation = xlLandscape
    End If
End With
End Sub

You might find this helpful https://groups.google.com/forum/#!topic/microsoft.public.excel.programming/8wZfCNzEreA

 

[DavidB]

Thanks Footoo

That is exactly what I am looking for.

Cheers DavidB