McGimpsey & Associates

Hiding cells when printing

You can hide rows or columns before printing.

If you have a range that doesn't consist of entire rows or columns that you want to avoid printing, here's one way (it works for entire rows and columns, too):

On each worksheet that you want to hide a range, select the range (it can be non-contiguous) and give it a sheet level name of "NoPrintRange" (one way: In the Name box at the left of the formula bar, type the sheet name, then "!NoPrintRange").

Put this in the ThisWorkbook code module: Ctrl-click (Mac) or right-click (Windows, or Macs with 2-button mice) on the workbook title bar, choose View Code, paste the following in the window that opens, then click the XL icon on the toolbar to return to XL:

    '*********************************************************
    'Purpose:   Hide cell values when printing
    'Inputs:     None: A Sheet-level name of "NoPrintRange" must 
                    be defined for each sheet with a range to be hidden.
    'Returns:   None: Prints each selected sheet.
    '*********************************************************
    Private Sub Workbook_BeforePrint(Cancel As Boolean)
    	'J.E. McGimpsey  http://www.mcgimpsey.com/excel/noprintrange.html
        Dim vFontArr As Variant
        Dim oWkSht As Worksheet
        Dim rNoPrintRange As Range
        Dim rCell As Range
        Dim rArea As Range
        Dim i As Long
        Dim bOldScreenUpdating As Boolean
        
        Cancel = True
        With Application
            .EnableEvents = False
            bOldScreenUpdating = .ScreenUpdating
            .ScreenUpdating = False
        End With
        For Each oWkSht In ActiveWindow.SelectedSheets
            On Error Resume Next
            Set rNoPrintRange = oWkSht.Range("NoPrintRange")
            On Error GoTo 0
            If Not rNoPrintRange Is Nothing Then
                With rNoPrintRange
                    ReDim vFontArr(1 To .Count)
                    i = 1
                    For Each rArea In .Areas
                        For Each rCell In rArea
                            With rCell
                                vFontArr(i) = .Font.ColorIndex
                                If .Interior.ColorIndex = xlColorIndexNone Then
                                    .Font.Color = RGB(255, 255, 255) 'white
                                Else
                                    .Font.ColorIndex = .Interior.ColorIndex
                                End If
                            i = i + 1
                            End With
                        Next rCell
                    Next rArea
                    oWkSht.PrintOut
                    i = 1
                    For Each rArea In .Areas
                        For Each rCell In rArea
                            rCell.Font.ColorIndex = vFontArr(i)
                            i = i + 1
                        Next rCell
                    Next rArea
                End With
            Else
                oWkSht.PrintOut
            End If
            Set rNoPrintRange = Nothing
        Next oWkSht
        With Application
            .ScreenUpdating = bOldScreenUpdating
            .EnableEvents = True
        End With
    End Sub

This page last updated Friday, 17 October 2003

© Copyright 2001 - 2003 McGimpsey and Associates. Except where noted, all code on this site may be distributed under the Gnu GPL. Acknowledgement is appreciated.