Receipt printing - how to not show zero value rows

Question

I am setting up a receipt printout from Excel 2010. I have multiple items on the worksheet and can print everything ok.

The receipt is to be printed in a busy environment and as such we just want the operators to enter the numbers and press CTRL+P.

Not all the items on the receipt will be used:

Item 1     10:00
Item 2      0.00   <--- This is an example of the row I do not want to print
Item 3     10.00
Total      20.00

The number of items could increase over time so the solution must be able to include the entire print range. Use of the hide function is not an option as it takes to long.

The solution must require no action by the user as they are not 'computer people'.

All cells are locked except those which require data to be entered to minimise input errors. i.e. VAT calculations

I had tried a VB routine but with no luck, hence the question.

EDIT: The VB I had written was-

Private Sub Workbook_BeforePrint(Cancel As Boolean)
    Dim RngCol As Range
    Dim i As Range
        Set RngCol = Range("B1", Range("B" & Rows.Count). _
        End(xlUp).Address)
            For Each i In RngCol
            If i.Value = 0 Then
        i.EntireRow.Hidden = True
        End If
    Next i End Sub

I have tried Jeeped's suggestion but some how the page size has now changed - won't change back either? Although Jeeped's suggestion has done what I wanted it is now ignoring the header which is needed although I can move the info to the main sheet.

Answer 1

Use a conditional format rule. I always use rules based on formulas like =NOT($D1) to cover columns A:E depending on the value in column D but any of the others will do if you can determine criteria that equals zero. When you decide on how you want to handle the criteria, click Format and go to the Number tab. Choose Custom from the list down teh left and use ;;; (three semi-colons) for the Type:.

Click OK to accept the format and then OK to create the rule. Nothing in A:E will be visible if there is a blank or 0 in column D.

Alternate AutoFilter Method:

The individual worksheets do not have any standard events to do with printing but the workbook has a BeforePrint Event.

Go the workbook's VBE and locate ThisWorkbook in the Project Explorer then double-click it. Paste the following into the new pane on the right titled something like ThisWorkbook (Code).

Option Explicit

Private Sub Workbook_BeforePrint(Cancel As Boolean)
    Cancel = False
    With Worksheets("Sheet2")
        If .AutoFilterMode Then .AutoFilterMode = False
        With .Range(.Cells(1, 2), .Cells(Rows.Count, 2).End(xlUp))
            .AutoFilter Field:=1, VisibleDropDown:=False, _
                        Criteria1:="<>0.00", Operator:=xlAnd, Criteria2:="<>"
        End With
    End With
End Sub

Edit to suit the actual column that holds the numbers. I've used column B. Note that the AutoFilter requires a header row.

Tap Alt+Q to return to your worksheet. Any method of printing the page will result in the AutoFilter Method being applied to column B and hiding any blanks or zeroes (in the form of 0.00) in that column.

Before:         After:    

As you can see from the sample images, printing to a .PDF filters the code column. I've also chosen not to display the little drop-down arrow normally associated with an AutoFilter.

You can unhide the rows with conventional unhide commands, clear the filter or just turn the autofilter off to get the rows back.

This latter method is a bit more involved but it gets rid of (aka hides or filters out) the undesired rows rather than simply not displaying the text in them. If need be, the worksheet could be unprotected for the duration of the print cycle.