Adding Last-Row Data to a Page Footer

by Allen Wyatt
(last updated October 30, 2021)

3

Kristen has a worksheet that contains thousands of rows of data. In column B is the last name for the person in each row, and the rows are sorted using this last name as the primary key. When Kristen prints the worksheet, it would be nice if the footer could show, at the right side, the last name of the last person printed on that page. That would allow her to thumb through the printout and easily identify a page she needs.

There is no built-in ability to do this with Excel, but you can create a macro to do it. The tricky part is figuring out where the page breaks are located so that your macro can grab the last cell in column B for the page. Consider the following macro, which implements one way to figure out the page breaks:

Sub PrintNamesInRightFooter()
    Dim iLNCol As Integer
    Dim lPage As Long
    Dim lPr As Long
    Dim iFirstRow As Integer
    Dim iLastRow As Integer
    Dim sPrintArea As String

    iLNCol = 2     ' Column from which to grab name

    With ActiveSheet
        sPrintArea = .PageSetup.PrintArea

        For lPage = 1 To .PageSetup.Pages.Count
            lPr = ((lPage - 1) Mod (.HPageBreaks.Count + 1))
            If lPr = 0 Then
                iFirstRow = 1
                If sPrintArea > "" Then
                    iFirstRow = .Range(sPrintArea).Cells(1).Row
                End If
            Else
                iFirstRow = .HPageBreaks(lPr).Location.Row
            End If
            If (lPr + 1) > .HPageBreaks.Count Then
                iLastRow = .Cells(iFirstRow, iLNCol).End(xlDown).Row
            Else
                iLastRow = .HPageBreaks(lPr + 1).Location.Row - 1
            End If
            .PageSetup.RightFooter = .Cells(iLastRow, iLNCol)
            .PrintOut From:=lPage, To:=lPage
        Next
    End With
End Sub

The macro steps through each page in the active worksheet and calculates the last row by utilizing the .HPageBreaks collection. Each member of the collection can be used to determine the first row of each page, but the logic in the macro uses the row just before that to grab the desired name and stuff it into the right portion of the page footer. Since the footer is changing for each page, only a single page of the worksheet is printed at a time.

It should be noted that under some circumstances the use of the .HPageBreaks collection can cause errors in VBA. Microsoft details this issue at this ridiculously long URL:

https://support.microsoft.com/en-us/topic/you-receive-a-subscript-out-of-range-error-message-when-you-use-hpagebreaks-or-vpagebreaks-location-in-excel-ec4a8e22-9b58-349d-b160-f7a4f561c809

If you run the macro and you get an error (the one described at the URL above), you can try adding the following right after the "With ActiveSheet" line:

        .Cells(.UsedRange.Rows.Count + .UsedRange.Row - 1, _
          .UsedRange.Columns.Count + .UsedRange.Column - 1).Select

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 (11887) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365.

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

Removing Pictures for a Worksheet in VBA

Excel allows you to add pictures to your worksheet, even within a macro. However, you might have a bit harder time ...

Discover More

Creating Custom Labels

There is a whole passel of labels pre-defined in Word. You are not limited to this passel, however; Word allows you to ...

Discover More

Envelopes and Labels Dialog Box Slow to Open

We all like Word to be snappy and responsive. Sometimes, though, it can be much slower than what we desire. One such time ...

Discover More

Professional Development Guidance! Four world-class developers offer start-to-finish guidance for building powerful, robust, and secure applications with Excel. The authors show how to consistently make the right design decisions and make the most of Excel's powerful features. Check out Professional Excel Development today!

More ExcelTips (ribbon)

Putting Headers and Footers on Multiple Worksheets

You can easily create headers and footers for multiple worksheets by working with a selection set of the worksheets you ...

Discover More

Ampersands in Headers and Footers

Add an ampersand to the text in a header or footer and you may be surprised that the ampersand disappears on your ...

Discover More

Putting a Different Date in a Header

Today's date is easy to add to a header, but what if you want to add a date that is adjusted in some manner? Adding ...

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 9 - 3?

2021-10-31 16:52:09

Tomek

Just one more note: If unsure whether cells to the right of and below the data range are truly empty select these columns and press delete, then do the same with the rows, before running the macro. Even a single cell with just a space character in it (invisible) will affect UsedRange and may spoil the macro. Ctrl+End sends the cursor to the last used cell, but this is not reset automatically when you delete a cell content outside your used range. Depending on a version of Excel it may be reset when you close and re-open the file, but using ActiveSheet.UsedRange in the code does the reset too.


2021-10-31 00:14:44

Tomek

I propose a similar macro which depends on slightly different assumptions, which makes the logic simpler.
1. I assumed the data starts in cell A1 and is in contiguous range. There are no non-empty cells to the right of and below this range. I assume that all this data needs to be printed, hence:
2. The macro sets the print range to UsedRange, i.e., from A1 to the bottom right cell with data. It is important to set the print range explicitly for Excel to calculate number of pages and horizontal page breaks correctly. Also, this seems to minimize (but not 100% eliminate) the subscript-out-of-range error mentioned in the tip. It also helps if Application.PrintCommunication is explicitly set to True.
3. The first row contains Column Headings and will be repeated on all pages.
4. The data should fit on one page horizontally. The user can select landscape orientation and/or larger paper size for the printout to fit more columns of data. The user can also select the scaling in the page setup to fit to 1 page wide and leave the box for tall blank. This may limit the readability though. It is easy to check for page fit by going to Page Break Preview.
5. The macro will work with automatic horizontal page breaks as well as manually inserted.
6. The user can set a smaller print area manually in the spreadsheet, then comment out the line .PrintArea = ActiveSheet.UsedRange.Address, as long as the print area starts in column A (but not necessarily in the first row of data). The column headings will still be printed for the columns selected.
Note: ActiveSheet.HPageBreaks.Item(Pg).Location returns the leftmost cell in the print area just below the horizontal page break #Pg, therefore .Offset(-1,1) returns the cell above the page break in second column.
-------------------------------------------

Public Sub PrintNamesInFooter()
Dim LastRow As Long, Pg As Integer, NumPages As Integer
Dim TopLeft As Range, BotLeft As Range

Application.PrintCommunication = True 'make sure print settings and page breaks are communicated
'between Excel and printer

With ActiveSheet.PageSetup
.PrintTitleRows = "$1:$1"
.PrintTitleColumns = ""
.PrintArea = ActiveSheet.UsedRange.Address 'necessary for proper finding of all page breaks
NumPages = .Pages.Count
End With

LastRow = Range("Print_Area").Rows.Count

For Pg = 1 To NumPages
If Pg < NumPages Then
ActiveSheet.PageSetup.RightFooter = ActiveSheet.HPageBreaks.Item(Pg).Location.Offset(-1, 1) 'finds lastname in col. B just before page break
Else
ActiveSheet.PageSetup.RightFooter = Cells(LastRow, 2) 'on last page last Lastname is in last row of data in col. B
End If
ActiveSheet.PrintOut From:=Pg, To:=Pg
Next Pg
ActiveSheet.PageSetup.RightFooter = Empty
End Sub
------------------------------
I have an idea how to get the printout that spans more than a single page horizontally. If someone is interested, please reply to this comment or send me an e-mail.


2021-10-30 23:47:00

Tomek

The macro provided in this tip does the job, however I had hard time following its logic. I figured that the logic was for the macro to print all pages even if not all columns fit horizontally on a single page.

Let’s say the printout takes two pages horizontally and three pages vertically and the page order is set up to “Down then Over”. Footers on pages 1 and 4, 2 and 5, as well as 3 and 6, will be the same, which is correct. However, if the page order is “Over then Down”, most footers will be misplaced, so user must pay attention to the page order setting. The latter setting would have two or more consecutive pages for the same rows, while the former prints first column of pages then goes back to the top row for the consecutive columns.
What the macro is missing, in my opinion, is repeating headings for columns on each page and, if data spans more then one page horizontally, repeating row headings.

I will post another comment with a macro I propose that may fulfill Kristen's wish.


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.