Invisible Ink

If there are certain cells on your worksheet that you don’t want to print, you can hide the rows or columns they’re in before you print. Sometimes, however, there is other information in those rows and columns that you DO want to print.

There is no facility to hide individual cells, but you can change the color of the text to match the background of the cell. I’ll show you how I do it, but it’s not the only way (or the best way necessarily).

First, I create a style (Format>Style) called PrintWhite. If the background of your cells isn’t white, you’ll have to adjust this example to suit your particulars. I base the style on the Normal style, that is, I select a cell with the Normal style before creating PrintWhite. My style box looks like this:

PrintWhiteStyle.gif

As you can see, I uncheck all the boxes except font. I didn’t need to do that for this example, but since I know that the only attribute I will be changing is the font, I did it anyway.

Next, change the style for each cell that you want to hide while printing. In this example, I changed the style of all the even numbers to PrintWhite and left the odd numbers Normal.

PrintWhiteRange.gif

Finally, you’ll need some code in your WorkBook_BeforePrint event. (Alt-F11 to open the VBE, double click on ThisWorkbook under your project in the Project Explorer, and select Workbook and BeforePrint from the dropdown boxes).

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Application.EnableEvents = False
Cancel = True
Me.Styles("PrintWhite").Font.Color = vbWhite
ActiveWindow.SelectedSheets.PrintOut
Me.Styles("PrintWhite").Font.ColorIndex = xlColorIndexAutomatic
Application.EnableEvents = True
End Sub

The above event procedure changes the font color of the style to white, prints, then changes the color back to automatic.

12 thoughts on “Invisible Ink

  1. This is one of those annoying “features” that causes me extra mouse clicks 10 times a week.

  2. Well, that comment was for the next entry, the missing page breaks entry. I got confused about which comments go with which entry…..

  3. Wendy: How about now? I put the dashed line below intead of above. I think you’re right, the line above made it a little confusing.

  4. Ah, very nice! I just thought I was getting smarter, and looked at the comments to see why I had gotten confused in the first place!

  5. Hey there just found your Blog–did want to comment on hiding info in a single or group of Cells for printing. This will hide it both when you print and also when you are viewing the cell.

    Select cell or Cells-then Format->Cells->Custom and in the Custom box type ;;; These guys will hide the content of the cell from view–you can still see the formula-just not the result in the cell.

  6. Melissa: I like that better. Instead of changing the font in my style, I should change the number format. That will work on differently colored cells as well.

  7. A few years later, but MelissaC you are my hero. ;;; is exactly what I was looking for to hide the contents of a cell. No more messing around with font colours. Thank you so much. Rod.

  8. MelissaC, you just made my day. Thanks. I wish I knew why it works, though. What do the semicolons (or double quotes) mean?

    -Trevor


Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.