Getting Best Print Results

Hi,

I have a 1 page spreadsheet that I need to get the best look when printing. The Range is A1:E68 The issue is when I hide rows in this range it makes the spreadsheet smaller. So it doesn't fit the page correctly and the zoom could be larger to make it look better.

My Question is how to get the best zoom in the page setup?

I have successfully done this manually by looking at the pagebreakpreview and dragging the first line on the far left all the way to the right.

When I recoded a macro I get this:

ActiveWindow.View = xlPageBreakPreview
    ActiveSheet.VPageBreaks(1).DragOff Direction:=xlToRight, RegionIndex:=1

Is there another way to get this results? With out having to change the view of the window. Or maybe another option?

Here is a test workbook with script inside.

Thanks for looking...
Mike

Did you consider using:

    ActiveSheet.VPageBreaks(1).Location = ActiveSheet.Range("F1")

No I didn't think about that option, I guess that may work...Thank You...

I was hoping to get away from

ActiveWindow.View = xlPageBreakPreview

Which only works when I activate each sheet in the wb which I didn't want to do.


In order to use this snipit:

With wks
    On Error Resume Next
    .VPageBreaks(1).DragOff Direction:=xlToRight, RegionIndex:=1
    .HPageBreaks(1).DragOff Direction:=xlDown, RegionIndex:=1
    Cancel = False
    .PrintOut
    End With
    On Error GoTo 0
 
    Cancel = True
    ActiveWindow.View = xlNormalView

The script works I just thought there must be a better way...

Thanks, Mike