Excel will automatically decrease the print zoom % to fit to one page, but it won’t increase the zoom % to fit to one page. E.g. if you want to print on A3 instead of A4. I had a request to do this, so I wrote a macro to do it.
Excel’s built-in “Fit to” option in the Page Setup (see image below) only decreases the print zoom % because most sheets are too big to fit on a single A4 page at 100%.
There is no functionality to increase the zoom % to make the sheet expand to fill a single A3 page.
Instead of using trial and error or manually increasing the zoom percentage, we can create a macro to do the work for us.
This is also a good example of
- recording macros to discover code
- using Google to identify pieces of code that you may not find when you record a macro
- how you can amend recorded code to be more flexible
- debugging a macro that doesn’t work as expected
The sequence we will follow is shown below.
- Record a macro to change the zoom (scale) percentage to discover the code required.
- Do a Google search to identify code to return how many pages are being printed on the active sheet.
- Amend the code to progressively increase the zoom percentage until we end up with more than one page printed.
- Reduce the zoom percentage to go back to one page.
- Handle the maximum limit of the zoom %.
- Use debugging code to see why the code isn’t working.
- Record another macro to change the View (explained further below).
- Tweak the code to make sure the number of pages updates.
A file with the final macro can be downloaded at the bottom of the post.
If you are unsure how to use macros check out this post.
Note: this may not be the most efficient macro but it works.
Recording macro code
Even experienced macro writers regularly use the macro recorder. The macro recorder gives you the code you need, quickly and easily. You can then amend the recorded code to do what you want.
When you record a macro that does anything that involves printing you end up with a lot of VBA (Visual Basic for Applications) code. The screen shot below shows you the amount of code you get when you simply change the zoom percentage – I changed the zoom % on the Page Layout ribbon (see screen shot overlay – top right).
Note on speed
The Page Setup dialog interacts with your printer and that requires communications over your network. This impacts the speed of the macro. In general when you record a macro that involves the Page Setup dialog you should eliminate unnecessary lines of code because each line of code may require communication to the printer and slow the macro down.
In most cases when you record a macro, extra lines of code don’t necessarily slow the macro down very much, but when they involve the Page Setup dialog they usually do.
Because of the speed issue you need to scan the recorded code to find the zoom percentage change.
The piece of code that changed the percentage is
Google search – number of print pages
I then did a Google search to identify the code that tells us how many pages are printed. I came across a few pieces of code.
- one that uses the old Excel macro language (it still works now)
- then code that counted vertical and horizontal page breaks
- then a single line of code that works from Excel 2007 onwards
I decided to use the Excel 2007 piece of code.
The line of code that identifies how many print pages for the active sheet is
I then created the following piece of code that uses a Do Until Loop to increase the zoom percentage until the pages to print is greater than one. This Do Loop will keep increasing the zoom % by 1 until it causes the number of pages to exceed one or the zoom % equals 400. 400 is the maximum value for the zoom %. Trying to go above 400% will cause an error.
Below the Do Until Loop in the code I reduce the zoom percentage by one which should put the number of print pages back to one page before it went over to two.
Sub FillToOnePage() 'this macro increases the scale to fit % until the page ' fills the currently selected page size With ActiveSheet.PageSetup .Zoom = 100 Do Until .Pages.Count > 1 Or .Zoom = 400 .Zoom = .Zoom + 1 Loop If .Zoom <> 100 Then .Zoom = .Zoom - 1 End If End With End Sub
When I ran the code it didn’t work! The zoom % had stopped at 399% (400-1). What to do? It should work, why doesn’t it?
I used the Debug.Print command to monitor the number of pages that were being identified with each increase in zoom %. Debug.Print prints to the Immediate window in the VBA screen. You can capture values as the code is running.
To get to the VBA screen press Alt + F11.
To display (or hide) the Immediate window press Ctrl + G in the VBA screen.
I added this line of code to the loop under where I increment to zoom %.
Debug.Print "Pages " & .Pages.Count & " Zoom% " & .Zoom
I ran the macro and checked the Immediate window.
As you can see the number of pages didn’t change even though the zoom percentage had increased to 400. Why?
Change the Print View
Excel didn’t seem to be updating the number of printed pages.
In Excel you sometimes need to trigger an event to force Excel to do a calculation. I then thought of the Page Break Preview View (that’s the View that displays the non-printing areas as grey). That View shows the print with the page breaks.
I thought if I change between the Normal (grid) View and the Page Break Preview View Excel would update the number of pages printed.
I recorded another macro to change between Views – see below.
Sub Macro3() ' Macro3 Macro ' ActiveWindow.View = xlPageBreakPreview ActiveWindow.View = xlNormalView ActiveWindow.View = xlPageBreakPreview End Sub
I then added the code to switch between Views to the macro and it worked.
I have added comments (green lines) to the final code below to explain all the parts of the code.
Sub FillToOnePage() 'this macro increases the scale to fit % (zoom %) until the page ' fills the currently selected page size 'a variable used to capture the current view Dim CurrView 'capture the current view so we can return to it when finished CurrView = ActiveWindow.View 'use PageSetup commands in the active sheet With ActiveSheet.PageSetup 'start at 100% .Zoom = 100 'this command stops the screen from flickering ' it also speeds up the macro Application.ScreenUpdating = False '.Pages.Count is updated when you change the print view ' 400 is the limit of the zoom % ' this Do loop will stop when the number of page exceeds 1 ' or the zoom % hits 400 Do Until .Pages.Count > 1 Or .Zoom = 400 'set the view to normal ActiveWindow.View = xlNormalView 'increment the zoom % .Zoom = .Zoom + 1 'change the view to page break ' this forces a recalculation of the ' number of print pages ActiveWindow.View = xlPageBreakPreview Loop 'if the zoom % changed (not equal to 100) then reduce the ' zoom % by 1 which should return print pages to 1 If .Zoom <> 100 Then .Zoom = .Zoom - 1 End If 'return the view to the one used when the macro started ActiveWindow.View = CurrView 'turn off showing page breaks as dotted lines ' - this annoys some people ActiveSheet.DisplayPageBreaks = False End With 'turn the screen updating back on Application.ScreenUpdating = True End Sub