Hide rows, columns or cells when you print a sheet

Important message to visitors of this page

Ron de Bruin decided to remove all Windows Excel content from his website for personal reasons. If you want to know why, head over to rondebruin.nl.

Luckily, Ron was kind enough to allow me to publish all of his Excel content here.

Most of these pages are slightly outdated and may contain links that don 't work. Please inform me if you find such an error and I'll try to fix it.

Kind regards

Jan Karel Pieterse

AutoFilter or Advanced Filter is also a very good way to print only the things you want in the sheet, filter the range with your criteria and print the sheet. But it is not always possible to get the result with a filter, see the examples below for another way to hide Rows/Cells.

If you use one of the Print options in Excel the event below will automatic check the ActiveSheet name and run the code. This example will run if the ActiveSheet name = "Sheet1"

1) It will hide row 10:15
2) Print
3) Unhide row 10:15

Copy/Paste this event in the Thisworkbook module. If you not now how to do this check out this page.
Where do I paste the code that I find on the internet

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 hide 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

Make the Font white of a range

You can replace the black lines in the BeforePrint event above with the example code below if you want to use it. Or you can use the examples below in a stand alone macro.

1) It will make the font white
2) Print
3) Make the font black again

Range with one area

    With ActiveSheet
        .Range("B10:B14").Font.ColorIndex = 2
        .PrintOut
        .Range("B10:B14").Font.ColorIndex = 1
    End With

Range with more areas

    With ActiveSheet
        .Range("A1:A3,B10:B14,C12").Font.ColorIndex = 2
        .PrintOut
        .Range("A1:A3,B10:B14,C12").Font.ColorIndex = 1
    End With

All cells with a error

    With ActiveSheet
        .Cells.SpecialCells(xlCellTypeFormulas, xlErrors).Font.ColorIndex = 2
        .PrintOut
        .Cells.SpecialCells(xlCellTypeFormulas, xlErrors).Font.ColorIndex = 1
    End With

Use conditional formatting

Make the Font white with conditional formatting, see this pahe
http://www.contextures.com/xlCondFormat03.html#Print

Tip: If you only want to print unprotected cells then use Dave Peterson's example below

Pick out a cell that you can use for a flag (say X1--but you can use any cell you want, but keep it out of the print range). Then select your range to print.

Excel 97-2003: Format|Conditional formatting
Excel 2007-2013: "Conditional formatting" In the Styles group on the Home tab
Click on New Rule and choose "Use a formula to determine which cells to format"

The formula is:
=AND($X$1="hide",CELL("Protect",A1)=1)
make it white font on white fill (or blue on blue or ...)
Note: A1 in the formula is the first cell in your selection

When you want to print, enter "hide" in X1 to hide, print and clear X1 when you're ready.

Hide Empty rows, Print and unhide the rows

This example will loop through every row in this one column range (row 1 - 30)
Set rng = Sheets("Sheet1").Range("A1:A30")

If every cell in column A:G is empty it will hide that row. After the loop it print the sheet and then unhide the rows. Change "A1:G1" in the macro to the cells you want. You can also use this with non contiguous ranges like "B1,D1:G1"

Sub Hide_Print_Unhide()
    Dim rw As Long
    Dim rng As Range
    Dim cell As Range

    Application.ScreenUpdating = False

    Set rng = Sheets("Sheet1").Range("A1:A30")

    With rng.Columns(1)
        For Each cell In rng
            If Application.WorksheetFunction.CountA( _
               .Parent.Cells(cell.Row, 1).Range("A1:G1")) = 0 Then _
               .Parent.Rows(cell.Row).Hidden = True
        Next cell
        .Parent.PrintOut
        .EntireRow.Hidden = False
    End With

    Application.ScreenUpdating = True
End Sub

Ron de BruinExcel Automation