Adding Last-Row Data to a Page Footer

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


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

Assigning a Picture to a Folder

Windows allows you to customize many aspects of its user interface. One thing you can modify is how a folder's icon ...

Discover More

Performing Complex Sorts

One way you can easily work with data in a worksheet is to sort it into whatever order you find most helpful. Excel ...

Discover More

Tables within Tables

Inserting a table in a document is easy. Did you know that you can also insert a table within another table? Word allows ...

Discover More

Comprehensive VBA Guide Visual Basic for Applications (VBA) is the language used for writing macros in all Office programs. This complete guide shows both professionals and novices how to master VBA in order to customize the entire Office suite for their needs. Check out Mastering VBA for Office 2010 today!

More ExcelTips (ribbon)

Dynamic Headers and Footers

Do you want to change the headers and footers that appear on different pages of your printout? Here's how you can get ...

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

Using a Custom Date Format in a Header or Footer

Need to show a custom date in the header or footer of a printout? You'll need to resort to using a macro, as described in ...

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 nine minus 5?

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.