auto suppress unused rows when printing

[Erin]

I've got a worksheet designed to do estimates (for
products). It's set-up for the largest possible number
of items included in a quote for calculation purposes.
The end users are sales people, with end viewer being the
customer. I'd like to automatically suppress any unused
rows (product line item) when printing. How do I do
this???

 

[Tom Rollins]

I recently wrote a macro that does this that I use within a workbook that I
use like a template. It was needed because there is formatting that extends
to the worst case size of the sheets and most often they do not need all
that room. I placed the macro in in the ThisWorkbook object. You can
probably tune it to your needs.
---------------------------------------
Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim pntrng As Range


lastrow = ActiveSheet.Range("a65536").End(xlUp).Row
If ActiveSheet.Name = "Move_Info" Then
Set pntrng = Range("a6").CurrentRegion
lastcol = pntrng.Columns.Count
Else
lastcol = 12
End If
ActiveSheet.PageSetup.PrintArea = Range(Cells(1, 1), Cells(lastrow + 1,
lastcol)).Address
End Sub