Print current worksheet excluding a few cells

  • Thread starter Daniel Lidström
  • Start date
D

Daniel Lidström

Hi,

I'm writing a diary of what I do at work every day. At the end of the month
I print out a copy to report my time. But I don't want to print out what
I've been doing, only the time part. So a range of cells need to be empty.
How can I achieve this without cut/paste between print? A macro would be
great!
Thanks!
 
R

Ron de Bruin

Hi Daniel

You can make the text in the range White and print

Sub test1()
'Range with one area
With ActiveSheet
.Range("B10:B14").Font.ColorIndex = 2
.PrintOut
.Range("B10:B14").Font.ColorIndex = 1
End With
End Sub

Sub test2()
'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
End Sub


For more Print example's see
http://www.rondebruin.nl/print.htm
 
I

IC

Ron de Bruin said:
Sub test1()
'Range with one area
With ActiveSheet
.Range("B10:B14").Font.ColorIndex = 2
.PrintOut
.Range("B10:B14").Font.ColorIndex = 1
End With
End Sub
Click to expand...
This will only work if the "black and white" checkbox on the Page Setup -
Sheet tab is deselected.

Another method would be to use Range("B1").EntireColumn.Hidden = True/False
in place of Ron's ColorIndex lines. This will only work if the data you want
hidden is in it's own column (or row using EntireRow).

If you want it automated (ie to run when you click the print button) you
could use:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Range("B1").EntireColumn.Hidden = True
Application.OnTime earliesttime:=Now + TimeSerial(0, 0, 3),
procedure:="UnhideColumns"
End Sub

Sub UnhideColumns()
Range("B1").EntireColumn.Hidden = False
End Sub

This hides column B if print or print preview is selected, then calls
UnhideColumns 3 seconds later. This gives sufficient time for the output to
be sent to print. In the case of print preview, it will not activate until
the preview is closed.

Ian
 
D

Daniel Lidström

Another method would be to use Range("B1").EntireColumn.Hidden = True/False
in place of Ron's ColorIndex lines. This will only work if the data you want
hidden is in it's own column (or row using EntireRow).

If you want it automated (ie to run when you click the print button) you
could use:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Range("B1").EntireColumn.Hidden = True
Application.OnTime earliesttime:=Now + TimeSerial(0, 0, 3),
procedure:="UnhideColumns"
End Sub

Sub UnhideColumns()
Range("B1").EntireColumn.Hidden = False
End Sub

This hides column B if print or print preview is selected, then calls
UnhideColumns 3 seconds later. This gives sufficient time for the output to
be sent to print. In the case of print preview, it will not activate until
the preview is closed.
Click to expand...

This is good. But I want only a range to be hidden: Q5:Q29. The problem is
that I can't hide column Q because of merged cells. Q5 spans from Q5 to V5.
Is there a way to keep cells Q5:Q29 from being printed? I don't necessarily
want them hidden. Perhaps cut before print, paste back 3 seconds later?
 
Top