Set Print Area (Without using a Macro)

[craigg3]

I have an amortization table that I can put in different loan years which will vary my print area. Is there a way to have the print area adjust based on the last column that has actual data? All the cells down to the 30 year mark will have a formula in them, they just may not show up as blank cells if I dont use a 30 year schedule.

The sheet I need to have this work on is called "Amortized" and the print area will always start from column/row "B1" and the last column/row will be "I???"

I think this can be done with a a named range, but im not really sure how. Any help would be appreciated.

 

[PCL]

Perhaps can you define the name Print_Area as
=OFFSET(Sheet1!$B$1,0,0,COUNTA(Sheet1!$I:$I),8)
of course adjust sheet's name to your need.

 

[craigg3]

That doesn't seem to work. It still prints the whole amortization table.

 

[VoG]

Why without a macro? This could be done automatically with a macro that runs when you print

 

[craigg3]

Why without a macro? This could be done automatically with a macro that runs when you print

Yeah, I know how to do it with a macro, but would rather make it where other users dont have to click enable/disable macros during startup.

 

[PCL]

You're right it tooks me some runs to installed the formula: Check, double check the column I in the formula.
To continue on VoG suggestion why not to use a Private Sub Worksheet_SelectionChange(ByVal Target As Range)
testing if column "I" length changed.

 

[VoG]

Try this: right click the Excel logo just to the left of File on the menu bar, select View Code and paste in

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim i As Long, j As Long
i = ActiveSheet.Rows.Count
For j = i To 1 Step -1
    If Range("I" & j).Value <> "" Then Exit For
Next j
ActiveSheet.PageSetup.PrintArea = Range(Cells(1, 1), Cells(j, 9))
End Sub