How to prevent cells from printing in Excel

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.

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:

Screenshot
^{(Click image to enlarge)}

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

This solution doesn’t require you to take special action before and after each time you need to print the file. It builds on the idea of using a “Print Area”, as suggested by Terence and Don Livezey. Put the cell(s) that you don’t want to print outside the print area. If that’s good enough for you, you’re done.

But you might want the excluded cell(s) to appear inside the print area when you’re editing the worksheet. In that case, for each cell that that you want to display but not print:

Insert a “shape”. (A rectangle works best.) Position it over the cell where you want the data to appear. Format it visually as you want. It may be best to have no border line. Set the fill color to whatever you want for the background color, or no fill to let the underlying cell’s fill color show through (and make the shape itself invisible).
Configure the shape to display the content of the desired cell, by selecting the shape and then typing a reference (e.g., =D15) into the formula bar at the top. Do not just type into the shape; if you do that, the formula (=D15) will display literally.
Right click on the shape and select “Format Shape”. Under Properties, you can select if the shape is printed or not (by clearing the “Print object” checkbox). (In Excel 2013, this is under “Shape Options” → “Size & Properties” → “Properties”.)

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?

How to prevent cells from printing in Excel

Related

Recent Posts