Page Breaks Result In Blank Pages Printing When AutoFilter Used

Hello,

This is my first post.....

Thanks in advnace for any help you might provide.

In Excel 2007 -- I have created a pricing tool that uses the autofilter function to hide rows (which are full pages when printed) of product line items I dont want to include in the final proposal printout.

In order to get the format to look good when printed, I have to use page breaks throughout the sheet. The sheet includes about 300 rows which I have broken into 10+ pages (using page breaks)

Also note that I have specified the print area that excludes only the columns I want to print. For example, the column that contains the autofiter, is NOT in the print range as I dont want it showing up on the printout. Note that the columns in the print area do fit in the right/left print margins.

The problem is that when I apply the autofilter and hide rows, excel inserts blank pages for those pages that are hidden/filtered out. If I was printing to a printer I could just remove the blank pages and that would be fine. However, I am needing to print this to a PDF so I cant have blank pages.

To clarify further, Lets say I have a 10 page document/worksheet prior to autofiler being applied. After autofiler is applied it cuts its down to 3 pages on the screen which looks great. However, when I print it, excel spits out the 3 pages with text along with 7 blank pages in place of the pages are that are filtered out/hidden.

I have tried removing the hard page breaks and inserting blank rows to get the pages to fall on the natural page breaks but when I apply the filter it messes up my formatting.

I have searched the web for a macro that would print only visable rows on a sheet but have not been able to find one. I did find another post of the same issue, but the suggestion was to remove the page breaks, which does not solve my problem.

Any ideas would be much appreciated.

Thanks
Craig

Hi all,

Did anyone have any joy with this? I have the same problem, which I'll recap here just for clarity:

I have manually set page breaks on a spreadsheet, and also have print titles set, all of which work fine. However when the user filters a column and then tries to print just the filtered lines (which are all consecutive), it prints pages with just the heading for every page the filter has excluded. If they filter to something that would have been on page 3 on the whole document, they get it, plus 2 pages of headings.

Another forum suggested removing the print area, which I've done, but this hasn't solved my problem, which I have in both 2007 and 2003 versions.

Any help much appreciated!

Regards,
Su