Only Print Pages with Data

[meangreen]

I have a template that I have created for others to use. The sheet runs from A1:H1000 (that's 20 pages). Most of the time the user will only put data in the first 5,6,or 7 pages, but the remaining pages contain formulas and formatting. When the sheet is printed, all 20 pages are printed as a result. Is there a way to set the sheet up so that it it only prints the pages that contain data?

There are formulas on every page (they result in "" if not needed) and it is set up to repeat rows 1:4 at the top of every page.

 

[Anthony47]

In case the case is still pending...

You can use the following macro:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
TestCol = 1   '<<< 1=A, 2=B, 3=C, etc
For I = ActiveSheet.UsedRange.Rows.Count To ActiveSheet.UsedRange.Row Step -1
If Cells(I, TestCol).Value <> "" Then Exit For
Next I
ActiveSheet.PageSetup.PrintArea = "A1:H" & I
End Sub

Open the vba editor via Alt-F11; doubleclick on ThisWorkbook in the "VBA Project" frame, generally on the left; copy the above code and paste it into the frame at the right.
Modify the instruction marked <<< to identify a column that can be used to check if the row has or hasn't valid data in it; if you cannot commit on a single column, please post and we will find alternate method.

Return to excel and try a "Print Preview"; modify the contents of the sheet and check that you obtain what you need.

Bye.