Auto Adjust Print Area

**bgreen** · 06-04-2009, 04:24 PM

Is there a way to set Excel to only print rows that have data in them? I have an end user spreadsheet where the number of rows they have each time differs and I don't want them to have to manually set the print area each time. Any help would be much appreciated!

Thanks!
Matt

**Paul** · 06-04-2009, 04:43 PM

Hi Matt,

Not sure I follow you. If I have 1000 rows of data and print it, I get 12 pages (for example). If I delete 500 rows, only 6 pages come out of the printer. This is, of course, with no print areas set.

The easy suggestion is to simply remove your print areas and let Excel determine how many pages to print. It won't print what's not there.

Otherwise, a macro could be run on the "Workbook_BeforePrint" event that sets/clears/re-sets the print area for you.

**bgreen** · 06-04-2009, 05:03 PM

Hi Paul - thanks for the response! My challenge is that I have about 1000 rows of formulas that only actually show data in those fields if the user enters a number (when they enter a number in column A, it populates the other columns with vlookup's). The formula sets those columns to blank ("") if no number has been input So, even if they only input say 10 items, it is trying to print all 1000 rows because there are formulas setting those fieds to blank.

I was wondering if there was a way for it to default to only printing if there was actual data in the rows, not just blanks.

Thanks for your time!
Matt

**Paul** · 06-04-2009, 06:06 PM

Hi again, Matt.

To reset the print area each time you print or printpreview, you can use the following macro in the Workbook_BeforePrint event.

Please Login or Register  to view this content.

To add this code to your workbook, press ALT+F11 to open the VB Editor. In the left-hand column, double-click on the "ThisWorkbook" entry for your workbook then paste the code shown above into the window on the right. Close the VB Editor and then try printpreview. It should only show as many pages as you have values in column A.

I set the columns for the print area from A:G in the first line of code. Modify this to your actual columns (e.g. "$A$1:$BC$" - the second line of code finds the final row argument). If your last used column also changes, we can add code to find that as well.

Hopefully that helps!

**bgreen** · 06-09-2009, 10:02 AM

Paul - that worked perfect, thanks so much for the help!

Thanks
Matt

**steliosL** · 01-18-2013, 05:44 AM

Excellent tip Paul.
Thanks bgreen for the post.

**flash053069** · 01-01-2014, 02:26 PM

I have this exact issue with my file in 2010. I did as directed but it has not helped in 2010 version. Can you give me any suggestions?

**arlu1201** · 01-02-2014, 01:42 AM

flash,

Unfortunately you need to post your question in a new thread, it's against the forum rules to post a question in the thread of another user. If you create your own thread, any advice will be tailored to your situation so you should include a description of what you've done and are trying to do. Also, if you feel that this thread is particularly relevant to what you are trying to do, you can surely include a link to it in your new thread.