Hidden rows printing as blank pages

O

o2coolchris

New Member
Joined
Mar 23, 2007
Messages
11
What macro would I need to write to make my worksheet not print blank pages when I have hidden rows within my print area?

My current report has different 13 tables set up each table set to print on its own page. When the dropdown for "all managers" is selected, all tables (rows) are visible and all 13 pages need to print. However, when a specific manager is selected, I have a code written to hide rows for tables that are blank for that manager. When I go to print however, it prints a blank page where the hidden table is located. The hidden tables are always going to be in the middle of the report, meaning i need to have pages print before and after the hidden areas.

Thanks for any help!
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
Sort by date Sort by votes
Probably the easiest way to do it would be to define each section of the report as its own page (go to View --> Pagebreak View and force a page break between each section of the report), then you can just have a print macro to print specific pages of the report.

Here's the basic code:
Code: 
Sheets("Sheet1").PrintOut From:=1, To:=2, Copies:=1, Collate:=True

You can just use the same rules that you use to determine which "pages" of the report are visible to determine which pages should be printed in From and To. If there is a chance it won't be a solid range of pages, then you would need to do one Print command for each individual page you want to print.

That should at least get you started... check out the help files for the PrintOut command if you need more info on the syntax.
 
Upvote 0
Check out the special cells method (visible cells), use something like this...
Code: 
On Error Resume Next
Sheet1.UsedRange.SpecialCells(xlCellTypeVisible).PrintOut
 
Upvote 0
You must log in or register to reply here.

Similar threads

tlc53
  • Question
Don't print blank pages, where page breaks are contained between hidden rows
Replies
0
Views
617
tlc53
tlc53
O
  • Question
Hiding Print area line
Replies
6
Views
242
Peter_SSs
Peter_SSs
N
  • Question
Count of No of Page to be Printed with YES or NO options
Replies
1
Views
121
Peter_SSs
Peter_SSs
D
  • Question
Very weird print margin problem, need help !
Replies
0
Views
136
dicklim39
D
R
  • Solved
Printing Multiple Ranges with Adjusted Formatting; to Multiple PDF Sheets in a Single PDF Document
Replies
2
Views
727
reberryjr
R

Forum statistics

Threads
1,214,952
Messages
6,122,454
Members
449,083
Latest member
Ava19

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