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.

1. Record a macro to change the zoom (scale) percentage to discover the code required.
2. Do a Google search to identify code to return how many pages are being printed on the active sheet.
3. Amend the code to progressively increase the zoom percentage until we end up with more than one page printed.
4. Reduce the zoom percentage to go back to one page.
5. Handle the maximum limit of the zoom %.
6. Use debugging code to see why the code isn’t working.
7. Record another macro to change the View (explained further below).
8. 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

ActiveSheet.PageSetup.Pages.Count

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?

Debugging

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

Download Example File