Perhaps not so simple, but relatively straightforward. You can't do it
with formatting. Either hide the unused columns/rows manually if the
user won't change the print area, or use a macro to do it:
Public Sub HideAllButPrintArea()
Dim rPrintRange As Range
Dim rFirst As Range
Dim rLast As Range
Application.ScreenUpdating = False
With ActiveSheet
.Cells.EntireColumn.Hidden = False
.Cells.EntireRow.Hidden = False
If .PageSetup.PrintArea <> "" Then
Set rPrintRange = .Range(.PageSetup.PrintArea)
Else
Set rPrintRange = .UsedRange
End If
With rPrintRange
Set rFirst = .Cells(1)
Set rLast = .Cells(.Count)
End With
If rFirst.Row > 1 Then _
.Range(.Cells(1, 1), rFirst(-0, 1)) _
.EntireRow.Hidden = True
If rFirst.Column > 1 Then _
.Range(.Cells(1, 1), rFirst(1, 0)) _
.EntireColumn.Hidden = True
If rLast.Row < .Rows.Count Then _
.Range(rLast(2, 1), .Cells(.Rows.Count, 1)) _
.EntireRow.Hidden = True
If rLast.Column < .Columns.Count Then _
.Range(rLast(1, 2), .Cells(1, .Columns.Count)) _
.EntireColumn.Hidden = True
End With
Application.ScreenUpdating = True
End Sub
This routine used the UsedRange as a proxy for PrintArea if PrintArea
has not been set. Note that UsedRange is notoriously squirrelly, and the
exact results may depend on the version of XL. In most cases, closing
then reopening the worksheet will reset the UsedRange properly.
You can have this update on opening the workbook if you also put this in
the ThisWorkbook code module (see
http://www.mcgimpsey.com/excel/modules.html
for more on where to put your code):
Private Sub Workbook_Open()
HideAllButPrintArea
End Sub
