Written by Allen Wyatt (last updated October 30, 2021)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365
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:
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.
Create Custom Apps with VBA! Discover how to extend the capabilities of Office 2013 (Word, Excel, PowerPoint, Outlook, and Access) with VBA programming, using it for writing macros, automating Office applications, and creating custom applications. Check out Mastering VBA for Office 2013 today!
When adding headers or footers to your worksheets, you may want to include the date that the workbook was last edited. ...
Discover MoreDo you find that there is a lot of extra space around the data on your worksheet when it is printed? Changing the margins ...
Discover MorePage numbers in Excel headers and footers have always been a source of frustration for users. This tip recounts one ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
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
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.
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.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2024 Sharon Parq Associates, Inc.
Comments