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.


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


Or hide all rows with a blank cell in column A



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


Range with more areas


All cells with a error



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"
05/09/2024
Web design by Will Woodgate