Only Print Pages with Data

meangreen

meangreen

Board Regular
Joined
Jan 29, 2007
Messages
169
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.
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
Sort by date Sort by votes
In case the case is still pending...

You can use the following macro:
Code: 
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.
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

O
  • Question
Hiding Print area line
Replies
6
Views
234
Peter_SSs
Peter_SSs
B
  • Question
footer on last page only
Replies
4
Views
262
John_w
J
R
  • Question
Print handouts for multiple employees that use different values
Replies
3
Views
115
jolivanes
jolivanes
E
  • Question
Print routines
2
Replies
13
Views
670
MARK858
MARK858
S
  • Question
Print to last column & row that has data
Replies
1
Views
172
ShawnSPS
S

Forum statistics

Threads
1,214,650
Messages
6,120,734
Members
448,987
Latest member
marion_davis

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top