PDA

View Full Version : Print area...



ragamuffin
12-12-2006, 12:46 PM
I have a worksheet where the print area changes daily. Is there a macro that can somehow run in the background and auto adjust to the populated cells, so that I do not have to manually do set it up everytime?

thanks!

ragamuffin

p.s. I know I have asked for a lot of help lately, and I would like to thank everyone for all of their contributions, advice, and work! :hi:

CBrine
12-12-2006, 01:01 PM
ragamuffin,
You don't need code for this.

1. Select File....Page Setup
2. Click on the "Fit To" radio button.
3. Clear the wide by text box.
4. Press OK.

This setup will always select everything in the sheet based on width for the page sizes.

If you need code, use this


'Setup printing options
activesheet.PageSetup.Zoom = False
activesheet.PageSetup.FitToPagesTall = False
activesheet.PageSetup.FitToPagesWide = 1


HTH
Cal

ragamuffin
12-12-2006, 01:36 PM
Will this Fit to method work if the page is filtered? I did what you said, but the print was only of the first 3 rows...I would actually like to stay away from code on this one, so I have not attempted your code as of yet.

CBrine
12-12-2006, 02:21 PM
I've attached a screen print to the attached excel workbook, that shows how the settings need to be set.

Cal

PS-It should show exactly what you have on your screen. One thing to note, is if you have manual page breaks setup will not work.

Charlize
12-13-2006, 03:20 AM
I think that when you set the print area on all the cells and you then apply a filter on it, it will only print the rows that are viewable.

To apply a printarea you could use this :
Sub Define_print_area()
Dim norows As Long
norows = Range("A" & Rows.Count).End(xlUp).Row
ActiveSheet.PageSetup.PrintArea = Range("A1:H" & norows)
End Sub
To remove the printarea use = ""

Charlize

CBrine
12-13-2006, 07:23 AM
Charlize,
The problem with setting a static print range, even when it's set programmatically. It's still static, so if you add something outside of the print range, it will not print. I've fallen prey to this a few times, where reports I added to after the print range was set, were truncated. The solution I suggest is a dynamic at print time, it will always print all printable data with in the actual worksheet.
Your code added to the before_print event would work similiarly, but if it can be done without code, I would go with that option.

Cal

CodeMakr
12-13-2006, 10:40 AM
The key is making sure the "print area" box on the sheet tab of page setup is blank. That along with designating 1 page wide will get you everything (except hidden rows/columns).

CBrine
12-14-2006, 07:32 AM
CodeMakr,
Good point, I've been doing this so long I forgot all about that. I haven't set a print area for a while.

Cal

ragamuffin
12-14-2006, 05:53 PM
Hey! It works! That was really simple to do. Thank you everyone for your contributions.

One final question, though. The page is shrunk down considerably when I look at the print preview. Is there a way to make it fill the maximum amount of space on the page. I am printing landscape and there is about 1/3 of the page not being filled. The print area is correct, all the info is there, just a bit small. Is there an easy way to correct this one as well?

THANKS again to everyone!

CBrine
12-15-2006, 08:51 AM
It will print everything in the actual sheet. The only way to adjust it is to adjust your column width's, and make sure NOTHING exists outside of the area with details. Hidden formula's, etc... I would highlight the blank columns that are appearing and just delete them if you know nothing exist in them. You could have a cell with a space in it.

HTH
Cal

CodeMakr
12-15-2006, 09:37 AM
Make sure your scaling fit-to "pages long" setting on the page setup box is blank....only setting your pages wide to 1. That will force fit your data to your page width but not limit your page length. (This is assuming your print area is being forced into 1 page wide by 1 page long)

ragamuffin
12-16-2006, 09:55 AM
Thanks Cbrine and Codemakr! It is all working like a charm now. :)