Hello everyone,

I have a document with 3 visible tabs in it. They all populate based on one or two drop down boxes which are populated/validated based on values from hidden pivot tables (via dynamic named ranges). What I would like to be able to do is avoid unnecessary wasted paper. Because one selection could yield 5 rows of data that print on one page and another could yield 77 rows of data that prints on 4 pages, I only want my print area to be the area that contains data. The cells that do not technically contain data are made to be "" via formulas. So, here is what my question boils down to:

How do I change the print area of a tab to only print where data is present, all happening automatically when the user hits the print button in the standard toolbar?

I hope I am not asking the impossible. Thanks for your time.

Scott

Dependent upon your data you could use something like this.

***Untested***

Dim r as Long
Dim adrss as string

r=[=count(A:A)]
adrss="A1:C" & r

ActiveSheet.PageSetup.PrintArea = adrss

Which you can place in the worksheet event BeforePrint, to redifine the print area.

You will obviously need to change the references to suit your data, but hopefully this will help.

Thanks for the reply. Sounds interesting. Where do I find the BeforePrint worksheet event? I'm fairly new to VBA.

Scott

ALT + f11 to open the VBA Editor, then place the code in the worksheet module, which you can find using the project explorer.


Private Sub Workbook_BeforePrint(Cancel As Boolean)

Dim r As Long
Dim adrss As String

r=[=count(A:A)]
adrss = "A1:C" & r

ActiveSheet.PageSetup.PrintArea = adrss

End Sub


In the above example if you have 10 numbers in column A, then this will set the print area to A1:C10. The setting of this range is where this will need some work, which really depends upon how you data is returned. Are there any blank lines, is it text or numbers etc? If you want some help with this, post a cut down version of your spreadsheet, and I can take a look (Only got xl97 on the machine I'm using).

Whilst you're in the Worksheet module you can have a look at other events associated at this level, using the drop down menus at the top, select worksheet in the left one, and in the right there are all the events.

Let me know how you get on.

Sorry, I still have some questions.

I can look at the code and see what it is doing (going to do). I will need to change the count formula to the match formula because it will still find things in cells that I don't want to print, since they contain formulas. The formulas in these cells is similar to =if(a10>c5,"",blahblahblah).

when I hit Alt + F11, I see "funcres", which I can not get into without a password. I also see "VBAProject (name of my doc)". Under that, MS XL objects. Under that, all of the sheets in the form of "Sheet # (sheet name)" and the last thing is "This Workbook". I don't see anything else.

So, do I double click the sheet in qustion and paste the above code? Or is there something else I need to do?

Then, if I do paste the above code, how does Excel know to assocaite this code with the pressing of the print button? I'm sorry, I'm not understanding.

Scott

Hi Scott.

Took me a while to get to grips with events, and I am still far from being an expert, so don't worry.

I had a typo in my last reply (Sorry), I should have said the workbook module, not the worksheet. In which case you need to double click the workbook (This Workbook) and paste the code in there.

All code is called into action when an event happens, this can be pressing a button, selecting a cell, saving the workbook... or many other actions.

One of these events in the workbook module is BeforePrint, which excel looks to see if there is any code to run in this event before actually printing the document.

There are a whole host of events associated with different parts of an Excel workbook, and can be very useful, and you can view these events by selecting them from the dropdown menus I mentioned above. I have also posted a quick screen shot to show where on my setup I can select these.

As for the count part of my code, this may not necessarily find things you do not wish to print as this only counts numbers, and will ignore any cell with the value "" as returned by your code. You are right though that this is where you will need work most on as I did not know the structure of you workbook, and therefore could not advise best as to how to arive at the row number of the last row which you want to print.


Hope I am starting to make this a bit clearer.

Stu.

Hey Scott and Stu,

CountA will count each cell in which there is a value or a formula that returns a null string. So if the formula returns "" then it will be included in the CountA result.

You might want to use a CountA-Countblank combination instead.

Here's a weird alternative. Once again place in the ThisWorkbook module.

Cheers for that Eric.


The reason I used count, was to avoid counting the null strings, as these were not required.

Anyway that's me done now for the weekend, I hear the call of a cold beer. I will pick up again on Monday, but I am sure that Eric will be able to help you much more than me Scott.

Good luck.

Mmmmm... Beer

Eric,
The code didn't work for me. I still received 4 pages when only 1 row of data was selected. Does it matter that columns A and B are hidden? I even tried to change the A1 reference to C1, where my printable data starts. Didn't work either.

I tried Stu's code, but substituted match "" for the count function, and it worked.

Here'e a weird question for you. I have columns C:AG with data that are printing. OK, a couple of the columns in that range are hidden. But, when I hide three more columns that aren't needed for this report, the white vertical borders on my column headings will no longer print. I've done enough one-change then print testing to verify that the hiding onf the non-needed columns is causing the issue. Have you heard of this issue before? Or, am I just crazy? You really don't have to answer that last question.

Scott

Once I figure this out, my next step is to add in the code from a previous post that will automatically autofit the visible columns before printing.

Scott,

Are you still having problems with this? I had a look at Erics code this morning and it looked very good to me, very inventive.

With regards to the issue you are having with borders disappearing I have come across this before, and it is very annoying, it's all to do with which cell Excel decides a border "belongs" to. I have not looked into any sort of automatic fix for this, but here is what I do to ensure they are kept:

Highlight the cell that the borders are disappearing from.
-> Format
-> Cells
-> Borders
-> None
-> OK
-> Format
-> Cells
-> Borders (Choose the borders you wish)
-> OK

This will then associate your borders with the correct cell.

Hi Scott,

Try this instead.



Hidden columns should not matter.