Hide Data Print and Unhide with the BeforePrint event


Note
: Check out also this page : Print Examples

AutoFilter or Advanced Filter is also a very good way to print only the things you want in the sheet, filter the range with your criteria and print the sheet. But it is not always possible to get the result with a filter, see the examples below for another way to hide Rows/Cells.

If you use one of the Print options in Excel the event below will automatic check the ActiveSheet name and run the code. This example will run if the ActiveSheet name = "Sheet1"

1) It will hide row 10:15
2) Print the WorkSheet
3) Unhide row 10:15

Copy/Paste this event in the Thisworkbook module of your workbook.


Private Sub Workbook_BeforePrint(Cancel As Boolean)
If ActiveSheet.Name = "Sheet1" Then
Cancel = True
Application.EnableEvents = False
Application.ScreenUpdating = False
With ActiveSheet
.Rows("10:15").EntireRow.Hidden = True
.PrintOut
.Rows("10:15").EntireRow.Hidden = False
End With
Application.EnableEvents = True
Application.ScreenUpdating = True
End If
End Sub

Or hide columns (this example hide column B and D)


With ActiveSheet
.Range("B1,D1").EntireColumn.Hidden = True
.PrintOut
.Range("B1,D1").EntireColumn.Hidden = False
End With

Or hide all rows with a blank cell in column A


With ActiveSheet
On Error Resume Next
.Columns("A").SpecialCells(xlCellTypeBlanks).EntireRow.Hidden = True
.PrintOut
.Columns("A").SpecialCells(xlCellTypeBlanks).EntireRow.Hidden = False
On Error GoTo 0
End With


Make the Font white of a range

You can also use this in the BeforePrint event above to make the text color white for a moment.

1) It will make the font white
2) Print the WorkSheet
3) Make the font black again

Range with one area


With ActiveSheet
.Range("B10:B14").Font.ColorIndex = 2
.PrintOut
.Range("B10:B14").Font.ColorIndex = 1
End With

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

All cells with a error


With ActiveSheet
.Cells.SpecialCells(xlCellTypeFormulas, xlErrors).Font.ColorIndex = 2
.PrintOut
.Cells.SpecialCells(xlCellTypeFormulas, xlErrors).Font.ColorIndex = 1
End With


There are many more options that you can use in the BeforePrint event or in a stand-alone macro.

For example this macro will loop through every row in this one column range in the sheet "MySheet" (row 1 - 30)
Set rng = Sheets("MySheet").Range("A1:A30")

If every cell in column A:G is empty in the row it will hide that row. After the loop it print the sheet and then unhide the rows. Change "A1:G1" in the macro to the cells you want to check. You can also use this with non contiguous ranges like "B1,D1:G1"

Sub Hide_Print_Unhide()
Dim rw As Long
Dim rng As Range
Dim cell As Range

Application.ScreenUpdating = False
Set rng = Sheets("MySheet").Range("A1:A30")

With rng.Columns(1)
For Each cell In rng
If Application.WorksheetFunction.CountA( _
.Parent.Cells(cell.Row, 1).Range("A1:G1")) = 0 Then _
.Parent.Rows(cell.Row).Hidden = True
Next cell
.Parent.PrintOut
.EntireRow.Hidden = False
End With

Application.ScreenUpdating = True
End Sub
11-May-2021
Web design by Will Woodgate