How to show columns/cells in sheet but hide them in print?

[Jeff Korn]

I have an Excel 2003 worksheet with some content.

One column - or more prceisely a couple of cells) should be displayed but not printed (on paper).

How can I achieve this?

Or alternatively is there a way to temporarily hide a range of cells in visual sheet and printed version?

Jeff

 

[Norman Jones]

Hi Jeff,

Apologies if this shows up twice but I am unable
to locate my original response.


In the ThisWorkbook module (see below),
try:

'=============>>
Option Explicit

'-------------------->>
Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim SH As Worksheet
Dim rCell As Range
Dim i As Long
Dim j As Long

Set SH = Me.Sheets("Sheet1") '<<==== CHANGE
Set Rng = SH.Range("A2, A4, A6") '<<==== CHANGE

ReDim Arr(1 To Rng.Cells.Count)

For Each rCell In Rng.Cells
j = j + 1
Arr(j) = rCell.Interior.ColorIndex
Next rCell

Rng.Interior.ColorIndex = xlNone
Application.OnTime Now, "AfterPrint"

End Sub
'<<=============

Change:

Set Rng = SH.Range("A2, A4, A6") '<<==== CHANGE

to reflect the cells of interest.

This is workbook event code and should
be pasted into the workbook's ThisWorkbook
module *not* a standard module or a sheet
module:

Right-click the Excel icon on the worksheet
(or the icon to the left of the File menu if your
workbook is maximised)
Select 'View Code' from the menu and paste
the code.
Alt-F11 to return to Excel.


In a standard module (see below),
paste the following code:

'=============>>
Option Explicit

Public Rng As Range
Public Arr() As Long

'-------------------->>
Public Sub AfterPrint()
Dim rCell As Range
Dim j As Long

For Each rCell In Rng.Cells
j = j + 1
rCell.Interior.ColorIndex = Arr(j)
Next rCell

End Sub
'<<=============

Alt-F11 to open the VBA Editor
Menu | Insert | Module
Paste the above code
Alt-F11 To return to Excel

Save the file.

 

[JLGWhiz]

I think the default for Interior.ColorIndex = xlNone.
Would it not be more effective to make the Font.ColorIndex = xlNone?

 

[Norman Jones]

Hi Jeff,

Please replace my suggested code with
the following version:

In the ThisWorkbook module, paste:

'=============>>
Option Explicit

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim SH As Worksheet
Dim rCell As Range
Dim i As Long
Dim j As Long

Set SH = Me.Sheets("Sheet1") '<<===== CHANGE
Set Rng = SH.Range("A2, A4,A6") '<<===== CHANGE

ReDim Arr(1 To Rng.Cells.Count)
ReDim Arr2(1 To Rng.Cells.Count)

For Each rCell In Rng.Cells
j = j + 1
Arr(j) = rCell.Font.ColorIndex
Arr2(j) = rCell.Interior.ColorIndex
Next rCell

With Rng
.Font.ColorIndex = 2
.Interior.ColorIndex = 2
End With

Application.OnTime Now, "AfterPrint"

End Sub
'<<=============

In a standard module, at the top of the module
and before any other procedures, paste the
following code:

'=============>>
Public Rng As Range
Public Arr() As Long
Public Arr2() As Long

'-------------------->>
Public Sub AfterPrint()
Dim rCell As Range
Dim j As Long

For Each rCell In Rng.Cells
j = j + 1
With rCell
.Font.ColorIndex = Arr(j)
.Interior.ColorIndex = Arr2(j)
End With
Next rCell

End Sub
'<<=============

 

[Norman Jones]

Hi JLG.

Would it not be more effective to make the Font.ColorIndex = xlNone?

Thank you for your query.

Whilst a cell's fill colour may be set to xlNone,
I do not think that this value has a useful significance
for the cell's font index.

In fact, the intent was temporarily to remove any
fill colour and hide any text by setting each to
white (2) and , after the print operation, restore
the memorised values.

As a result of your question, I reviewed my
response and realised that the code was not that
which I had intended to post.

Thank you again.