Prevents cells from printing in Excel

Question

Is it possible to prevent the contents of specific cells (or entire row or column) from printing in Excel? I need to maintain the cell size. I've been setting the text color to white before printing, but it seems there should be an automagic way of doing this.

Answer 1

You can apply normal (not conditional) formatting to achieve this. Select the cell, row, column in question and go to Cell Formatting (Windows shortcut it Ctrl + 1).

For number format select Custom and for Type set the format to be:

"";"";"";""

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

Answer 2

You can "hide" entire rows or columns by right clicking the row/column and selecting hide. This will prevent the row/column to be printed. If you have multiple rows/columns you can highlight them by "click drag" if the are next to each other or by "crtl click" if they are not next to each other. When rows/columns are hidden their number/letter is not shown. To unhide them just highlight the rows/columns on either side and "right click" and select unhide.

I am not sure if you can prevent individual cells from printing other than changing the font Color. You maybe able to automate this with conditional formatting.

Answer 3

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 it.

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 tohide columns (this example hide column 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

Answer 4

Besides hiding columns or rows as is suggested above. You might look at Custom Views as a simple way to apply and un-apply what you don't want to show.

For instance if you print one column for a salesperson, another for a warehouse, and another with everything, you may create a custom view for each and easily switch to either.