Thread: Hide cells from printing

I know similiar issues has been covered in this forum, but none of the previous threads solved my problem.

I have several, up to maybe 100 cells in different place in a worksheet, that we don't want to be printed. Until now we changed the text colour for the cells to white, but we noticed that the PDF-files wills til have the "invisible" text. Copy the document text to let say notepad, will show the "hidden" text in the cell. Therefore making the text white don't help.

We have to have another solution to it. Like a macro telling not to print the cell where white text is present.

Is it possible? Where to start? The company is using 2000 and I'm really a newbe on Excel. Please advise.

Can you not simply hide the rows/columns in question? I.e. Highlight the row/column, and select Hide?

where does pdf come into it?

We print to pdf and even if the white text is not visible it is there, and it should be secret.

Anyway, I almost got it. Will share the code when it is done even if it nor pretty (first macro).
I coded it myself, but I have one problem:

I get value of the white cell with (where c is the cell) and store in in a vector:
ObjArr(K) = c.Value

But if my self is refering to another cell like "=C53*H53", this don't count as a value? Right? Is there anything else without Value to use to fetch what is inside the cell?

Good afternoon terabyte

Hold down Ctrl and select all the cells that are going to be hidden. Then press Ctrl + 1 and go to the Protection tab and tick the box marked hidden. This won't actually do anything unless you protect the worksheet, but we just want to "mark" the cells you want to hide.

I don't know if you are doing your PDF creation via code but the macro below will go through the print range, make the marked cells hidden by setting their format to ";;;", the comment in the middle is where your print routine goes, then the marked cells are all set back to general.

Or you could split it into two macros and run the first one, create PDF then run the second one.

Sub test()
'Change the Worksheet's name below as applicable
Set a = Range(Worksheets("Sheet1").PageSetup.PrintArea)
With UsrCell
For Each UsrCell In a
If .FormulaHidden = True Then
.NumberFormat = ";;;"
End If
Next UsrCell

'Place your ptint routine here

For Each UsrCell In Worksheets("Sheet1").PageSetup.PrintArea
If .FormulaHidden = True Then
.NumberFormat = "General"
End If
Next UsrCell
End With
End Sub

HTH

DominicB

Hi terabyte

Originally Posted by terabyte

I know this way but I really have to many to do that. I have seen this tip on internet, when I searched. This is not an option.

Why not? You only have to select them and change the flag once. Then it's saved. All you have to do after that is run the macro which will Hide - Print - Unhide.

It's about as simple an answer as you're going to get.

DominicB

Yes. But how can I in the macro hide and unhide without doing it manually. Since I'm bad at excel programming I have no clue how to set the property of the cell to custom and ";;;" and then set it back ...

can I use in my code
c.NumberFormat = ";;;"

and then to unhide

c.NumberFormat = "General" ??

I will try that. Sorry I didn't see you posted this piece of code.

Thank you for all help. I manage to do that now.

Sub PrintWithoutWhiteText()
Dim LastColumn As Integer
Dim LastRow As Long
Dim LastCell
    If WorksheetFunction.CountA(Cells) > 0 Then
        'Search for any entry, by searching backwards by Rows.
        LastRow = Cells.Find(What:="*", After:=[A1], _
                    SearchOrder:=xlByRows, _
                    SearchDirection:=xlPrevious).Row
        'Search for any entry, by searching backwards by Columns.
                LastColumn = Cells.Find(What:="*", After:=[A1], _
                    SearchOrder:=xlByColumns, _
                    SearchDirection:=xlPrevious).Column
    End If
    
    LastCell = Cells(LastRow, LastColumn).Address(False, False)

    For Each c In Worksheets("Quote").Range("B1:" + LastCell).Cells
        If c.Font.ColorIndex = 2 Then
            c.NumberFormat = ";;;"
        End If
    Next
     
    ActiveWindow.SelectedSheets.PrintOut
    
    For Each d In Worksheets("Quote").Range("B1:" + LastCell).Cells
        If d.NumberFormat = ";;;" Then
            d.NumberFormat = "General"
        End If
    Next
   
End Sub

Look, all that is fine, but if all you want to do is hide a few cells on your page, Just set up Sheet #2 to pull all the values from sheet #1 and leave out the ones you want to hide. When you have to print, click over to sheet #2 and print. Cake brother! Screw the code before it screws you!

One option is to use the camera tool, it may work really well depending on which cells you want not to print, try using the camera tool to get the contents you want into another sheet that you'll print (nice because it'll include the cell formatting too.

To use it:
- Select cells to copy, and copy to clipboard
- Other paste options: picture (U), linked picture (I)

http://spreadsheetpage.com/index.php...e_camera_tool/