Have you ever wanted to setup your spreadsheet to print 1 page wide? It sounds like such a simple thing to do, and yet at first glance it appears not to be so easy in Excel’s VBA.

Now if you record a macro, or do some searching online, you’ll find that the method for setting up such settings using code, such as:

With ActiveSheet.PageSetup
    .FitToPagesWide = 1
    .FitToPagesTall = 5
End With

The problem being that you need to specify how many pages tall the document will be. Now I came across numerous post where people try to determine insert page breaks based on number of rows, or insert page breaks based on the height of rows, or I also came across posting stating to set the .FitToPagesTall to a extremely large number and Excel will automatically fit is properly.

Now, yes, these solutions will work, some requiring a lot more work than others, but as I found out, there is no need for such conveluted methods. One can merely set the .FotToPagesTall to False and Excel will resize according to fit the content by whatever value you specified in the .FitToPagesWide. So the finally code would merely be:

With ActiveSheet.PageSetup
    .FitToPagesWide = 1
    .FitToPagesTall = False 'Will take as many pages as required based on other settings
End With

Inversly, you could specify the .FitToPagesWide and set the value of .FitToPagesTall to False and Excel will resize accordingly.

One more simple technique in your bag of tricks!