suppress rows when printing

[erin]

Hi,
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.
Product line/rows includes description, quantity, price,
line total. The end users are sales people, with end
viewer being the customer. I'd like to *automatically*
suppress any unused product lines/rows when printing.
How do I do this??? Is it even possible?

FYI - I've also tried working with 2 documents (one
master, one for the sales guys to input items/qty/price)
and various functions to no avail... not sure if this is
a workable approach.

Open to any and all suggestions. Thanks!

 

[PegL]

One way would be to use a macro where you check the value
in each cell in, say column a, and set <cell
ref>.entirerow.hidden = true if it's blank.

 

[Dave Peterson]

I think I'd use Data|Filter|autofilter and show only the rows I want
(non-blanks???).

And teach the users to filter, print, and showall.

Or you could plop a button from the forms toolbar on the worksheet.
Give it a nice caption "Use me to print nice"

and assign it a macro like this:

Option Explicit
Sub testme()
Dim myRng As Range
With ActiveSheet
Set myRng = .Range("a3:a99")
On Error Resume Next
myRng.Cells.SpecialCells(xlCellTypeBlanks).EntireRow.Hidden = True
.PrintPreview 'printout
myRng.Rows.Hidden = False
End With

End Sub