Showing Print Preview for the Current Page

Written by Allen Wyatt (last updated July 6, 2024)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, Excel in Microsoft 365, and 2021


1

Michelle has a lengthy worksheet that she's working with. When she uses Print Preview for the worksheet, the page shown in the Print Preview dialog is always the first page. Michelle wonders if there is a way to make Print Preview automatically show the page of the worksheet on which she's working.

One way to approach this problem is to try working with print areas. If you select the area of the worksheet on which you are working and then set the print area equal to your selection, then when you display Print Preview, you should see only that print area.

If this does not accomplish your desire, then you might think you can resort to using a macro. Here's an example macro that may do the trick for you:

Sub PrintPreviewActivePage()
    Dim lActiveRow As Long
    Dim iActiveCol As Integer
    Dim iHPBs As Integer
    Dim iVPBs As Integer
    Dim lRow As Integer
    Dim iCol As Integer
    Dim iPage As Integer

    lActiveRow = ActiveCell.Row
    iActiveCol = ActiveCell.Column
    ActiveSheet.UsedRange

    If IsEmpty(ActiveCell.SpecialCells(xlCellTypeLastCell)) Then _
      ActiveCell.SpecialCells(xlCellTypeLastCell).FormulaR1C1 = " "
    If lActiveRow > ActiveCell.SpecialCells(xlCellTypeLastCell).Row Or _
      iActiveCol > ActiveCell.SpecialCells(xlCellTypeLastCell).Column Then _
      Exit Sub

    With ActiveSheet
        iHPBs = .HPageBreaks.Count
        iVPBs = .VPageBreaks.Count
        lRow = 0
        iCol = 0
        If iHPBs > 0 Or iVPBs > 0 Then
            For lRow = iHPBs To 1 Step -1
                If .HPageBreaks(lRow).Location.Row <= lActiveRow Then Exit For
            Next lRow
            For iCol = iVPBs To 1 Step -1
                If .VPageBreaks(iCol).Location.Column <= iActiveCol Then Exit For
            Next iCol
        End If
        iPage = (lRow + 1) + (iCol * (iHPBs + 1))
        .PrintOut From:=iPage, To:=iPage, Preview:=True
        MsgBox "Previewed page " & iPage
    End With
    If ActiveCell.SpecialCells(xlCellTypeLastCell).FormulaR1C1 = " " Then _
      Selection.SpecialCells(xlCellTypeLastCell).ClearContents
End Sub

This macro is actually a variation on any number of macros you could find with some sleuthing on the Internet. There are two key parts to it—first the macro figures out which "page" you are on in the worksheet, and then it uses the .PrintOut method with the Preview parameter set to True, resulting in Print Preview being invoked.

On some worksheets this macro may work great, but it is quite fickle in whether it will work or not. In most of my testing, I was not able to get it to work, unless I used very small worksheets. (In other words, very few rows and columns.) If you run it on a large worksheet, you'll quickly see that you get a "Subscript Out of Range" error in the loop that examines members of the .HPageBreaks collection. How this could happen when you aren't using a member that is out of range (lRow never varies outside the value returned by the .Count property) is baffling.

It seems to be a problem that Microsoft acknowledges, though. In fact, it is a problem they have acknowledged, yet never fixed, for years:

https://support.microsoft.com/en-us/help/210663/

The suggested solution on the web page doesn't really work, though. So, we are stuck with a macro that only works reliably on worksheets where you wouldn't need to calculate the page number because you are only working with a single page. Aargh!

The bottom line is that a macro-based approach may—for the foreseeable future—not be viable for Michelle's needs. That leaves us with just the print-area approach, described at the beginning of this tip.

Note:

If you would like to know how to use the macros described on this page (or on any other page on the ExcelTips sites), I've prepared a special page that includes helpful information. Click here to open that special page in a new browser tab.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (13578) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, Excel in Microsoft 365, and 2021.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Guidelines for Laser Printer Letterhead

Plan on using printed letterhead in your laser printer? Here are some tips and cautions about doing so.

Discover More

Changing the Default Desktop Icons

When you first install Windows, it places only a limited number of icons on your desktop. You can, however, tell Windows ...

Discover More

Adding Parentheses

Need to add parentheses around some word or phrase? Here's a quick macro that makes this simple edit in one step.

Discover More

Solve Real Business Problems Master business modeling and analysis techniques with Excel and transform data into bottom-line results. This hands-on, scenario-focused guide shows you how to use the latest Excel tools to integrate data from multiple tables. Check out Microsoft Excel 2013 Data Analysis and Business Modeling today!

More ExcelTips (ribbon)

Working with Multiple Printers

If you have multiple printers accessible to your computer, you may need a way to quickly print your worksheet on a ...

Discover More

Printing Just the Visible Data

In a large worksheet, you may want to display and print just a portion of the available data. Displaying the desired ...

Discover More

Automatic Selection of Portrait or Landscape

Should you print in portrait or in landscape? The decision can greatly affect the way your printout looks. Wouldn't it be ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] (all 7 characters, in the sequence shown) in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is one more than 7?

2024-07-09 16:09:57

J. Woolley

Here is another version of the Tip's macro that might be more reliable:

Sub PrintPreviewActivePage2()
    Dim previous, nRow, nCol, incr, page, VPB, HPB
    previous = ActiveWindow.View: ActiveWindow.View = xlPageBreakPreview
    nRow = ActiveCell.Row
    nCol = ActiveCell.Column
    incr = ActiveSheet.HPageBreaks.Count + 1
    page = 1
    For Each VPB In ActiveSheet.VPageBreaks
        If VPB.Location.Column <= nCol Then page = page + incr Else Exit For
    Next VPB
    For Each HPB In ActiveSheet.HPageBreaks
        If HPB.Location.Row <= nRow Then page = page + 1 Else Exit For
    Next HPB
    ActiveSheet.PrintOut From:=page, Preview:=True
    ActiveWindow.View = previous
End Sub

Or you can switch the View from Normal to Page Layout or Page Break Preview. The three views are selected in the status bar at bottom-right of Excel's window or by use of the Ribbon. If you choose the Ribbon, pick
View > Page Layout (Alt+W+P) or View > Page Break Preview (Alt+W+I).
Page Break Preview includes page numbers; you might want to adjust the zoom. The following macro initiates Page Break Preview with 100% zoom before prompting for Print Preview's starting page number, then restores the inital zoom and view when done.

Sub PrintPreviewFromPage()
    Dim prevView, prevZoom, prompt, page
    prevView = ActiveWindow.View: ActiveWindow.View = xlPageBreakPreview
    prevZoom = ActiveWindow.Zoom: ActiveWindow.Zoom = 100
    prompt = "Active printer:" & vbLf & Application.ActivePrinter & vbLf _
        & vbLf & "Scroll to view page breaks and page numbers." _
        & vbLf & "Enter the first page number for Print Preview:"
    page = Application.InputBox(prompt, , 1, Type:=1)
    If page > 0 Then ActiveSheet.PrintOut From:=page, Preview:=True
    ActiveWindow.Zoom = prevZoom: ActiveWindow.View = prevView
End Sub

Notice you can scroll the window while Application.InputBox is displayed.


This Site

Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.