One way (from
http://www.mcgimpsey.com/excel/noprintrange
):
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:
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("rNoPrintRange")
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
'white
.Font.Color = RGB(255, 255, 255)
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
