Printing Fit to Page Width
Making printed pages format correctly from Excel can sometimes be difficult.
The most common printing-related request I receive is: “How do I get Excel to print all of my columns on the same page?”
Take Northwind’s Customers table for example.
I’ve just extracted about 6 columns into my worksheet.
If I Autofit the columns, then do a Print Preview, some of the columns end up on a page of their own.
Here is how to fit them all on the same page.
From the File menu, select Page Setup…
On the Page tab, make it so that it reads: Fit to 1 page(s) wide by __ tall.
(The __ is intentionally blank).
Excel will interpret the settings as: Fit to 1 page wide by ‘as many pages as necessary’ tall
As you increase the number of columns, Excel automatically fits them into a print out.
Though, once the text becomes too small to read, you’ll want to flip the Orientation to Landscape mode.
Mike:
I knew about this but this is another of those tips that I always seem to take for granted and never share with anyone else.
17 February 2006, 6:38 amfrank:
I bet the second most common printing-related request is: if there are several pages, how do I keep the column headers on each page?
Page Setup - Sheet - Rows to repeat at top.
17 February 2006, 9:54 amBillkamm:
I wrote some code to automatically choose the best page setup for a dynamically generated report awhile back. I believe this code assumes that there are no empty cells in your block of data. You may wish to use “UsedRange” if you are going to modify this for your use. Also, my numbers I use on my 3 constants might need tweaked a little. I have yet to determine the exact point (to the decimal) where Excel pushes you on to a new sheet
‘ Sum of Used Columns’ ColumnWidths (Max width for each category)
Private Const m_sngcReportPageWidth As Single = 80
Private Const m_sngcReportLandscapePageWidth As Single = 127.87
Private Const m_sngcReportLandscapeLegalPageWidth As Single = 150
‘ Determine the last cell in column A
lngLastUsedRow = LastUsedRowInColumnA(m_wbkReport.ActiveSheet.Name)
‘ Determine the last cell in row 1
intLastUsedCol = LastUsedColInRowOne(m_wbkReport.ActiveSheet.Name)
With m_wbkReport.ActiveSheet
‘ Determine total columns
intTotalCols = .UsedRange.Columns.Count
‘ Determine page width
sngPageWidth = 0
For intColumnCounter = 1 To intTotalCols
sngPageWidth = sngPageWidth + .Columns(intColumnCounter).ColumnWidth
Next intColumnCounter
‘ Make the report fit the entire page width
If sngPageWidth >= m_sngcReportLandscapeLegalPageWidth Then
‘ Make the report landscape and legal
.PageSetup.Orientation = xlLandscape
.PageSetup.PaperSize = xlPaperLegal
‘ Determine the number of pages tall
21 February 2006, 8:46 amlngPagesTall = CLng(lngLastUsedRow / 50)
If lngPagesTall = m_sngcReportLandscapePageWidth Then
‘ Make the report landscape and legal
.PageSetup.Orientation = xlLandscape
.PageSetup.PaperSize = xlPaperLegal
.PageSetup.Zoom = 100
ElseIf sngPageWidth >= m_sngcReportPageWidth Then
‘ Make the report landscape
.PageSetup.Orientation = xlLandscape
.PageSetup.PaperSize = xlPaperLetter
.PageSetup.Zoom = 100
End If
End With
Billkamm:
Is there a way to get the comments system to format code or at least maintain white space?
21 February 2006, 8:46 amAlex:
would you mind adding the code for LastUsedRowInColumnA() and LastUsedColInRowOne()
Also you are missing and END IF before the last END WITH
FWIW
alex
23 February 2006, 7:49 amRob van Gelder:
I’m on an Oracle training course this week..
No Excel on the computer, so I’m air-coding.
lngRow = Cells(Rows.Count, 1).End(xlUp).Row
23 February 2006, 1:52 pmlngCol = Cells(1, Columns.Count).End(xlToLeft).Column
Margaret Smyth:
I am using Excel 2003 SP2
I need to print 18 columns, print to fit 1 page wide by ** long. 3 of the columns contain large amounts of text. Whichever way I format it, the columns with large amounts of text do not expand in height to fill all the text when printing and cut off some of the text.
When viewing the sheet on the screen it the height is fully expanded to the height required to show all the text.
I hear this is a bug in Excel. Is this true?
5 April 2007, 3:35 amOla:
A program that I use on a daily basis is Fineprint.
8 April 2007, 4:10 amFineprint has been around for several year but is still remarkably unknown program. Actually I think this how they survive (no competition)
However, when companies do find this program they buy it by the 1000’s.
http:\\fineprint.com
JH:
Have any of you discovered how to do the same thing using MS Vista?
29 January 2008, 7:17 pmdustin:
I love you, i spent like 20 min trying to figure this out, then found this via google….
20 March 2008, 1:40 pm