How to prevent cells from printing in Excel

If you're using Office 2010, under "File -> Print -> Page Setup -> Sheet (tab)", the setting for "Print area" should be what you are looking for. In my example, only columns A:J will be printed; however, everything is shown when viewing on screen.

This is what the dialog looks like:

^{(Click image to enlarge)}

This may also be accessible as "Page Layout" → "Page Setup" → (corner button) → "Page Setup" → "Sheet":

You can apply normal (not conditional) formatting to achieve this. Select the cell, row, and/or column in question and go to "Format Cells", which is accessible through the ribbon ("Home" → "Cells" → "Format") or the keyboard shortcut Ctrl+1.

On the "Number" tab, select Category = "Custom" and for "Type" enter:

"";"";"";""

or simply

;;;

This tells Excel to display an empty string if the cell contains a positive number, negative number, zero or (non-numeric) text. So any value that is not an error will be hidden on the screen and when printed.

When I needed to do what you're saying, what I would do is:

Use the function in the code editor (VBA):

Private Sub Workbook_BeforePrint(Cancel As Boolean)

to hide the columns or rows, do the printout, and then unhide them.

Example:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
    If ActiveSheet.Name = "Sheet1" Then
        Cancel = True
        Application.EnableEvents = False
        Application.ScreenUpdating = False
        With ActiveSheet
            .Rows("10:15").EntireRow.Hidden = True
            .PrintOut
            .Rows("10:15").EntireRow.Hidden = False
        End With
        Application.EnableEvents = True
        Application.ScreenUpdating = True
    End If
End Sub

Or change the respective part to hide columns (this example hides columns B and D):

        With ActiveSheet
            .Range("B1,D1").EntireColumn.Hidden = True
            .PrintOut
            .Range("B1,D1").EntireColumn.Hidden = False
        End With

Or hide all rows with a blank cell in column A:

        With ActiveSheet
            On Error Resume Next
            .Columns("A").SpecialCells(xlCellTypeBlanks).EntireRow.Hidden = True
            .PrintOut
            .Columns("A").SpecialCells(xlCellTypeBlanks).EntireRow.Hidden = False
            On Error GoTo 0
        End With

Links:

• Hide rows, columns or cells when you print a sheet
• Prevent Printing of Specific Cells
• How do I add VBA in MS Office?

I solved the same issue by creating a separate sheet and copying a link to the information in there. To do this, highlight the whole of the original sheet and copy it (Ctrl+C). Go to the new sheet and right click the first cell. Select the link icon in the Paste Options. Everything pasted will be unformatted, so you'll have to correct this. Empty cells will contain zeros. This can be rectified by going to "File" → "Options" → "Advanced" and unchecking the "Show a zero in cells that have a zero value" box.

You can now delete any cells, rows or columns that you wouldn't like to appear on print outs. The Print sheet will update as you update the original sheet.

If, like me, you have a base sheet that you copy for different periods of time or purposes, you can create this print sheet for the base sheet. When you need a new pair, simply copy both sheets and the new print sheet will link to the new sheet.