I'm trying to figure out how many points exist between page breaks in Excel. I actually need to know the maximum amount of points that can be on a page, given the margin and zoom page settings.
The problem is that I can't come up with a function that will give the correct results when taking into account these settings. Is there an Excel property I can use to figure this out? I haven't had any luck in finding one.
Thanks!
Hi bbk5 and welcome to vbax!!
Would I be correct when I say that you're looking for the amount of points that can be on a Worksheet given the Margin and Zoom page settings? Or is it the amount of points that is available on the paper given the margin and zoom page settings?
New to the forum? Check out our Introductions section to get to know some of the members here. Feel free to tell us a little about yourself as well.
It's more the amount of points on the paper when the margin and zoom settings are taken into account. I am trying to manually place page breaks on the worksheet. However, I need to make sure I'm making as much use of each page as possible. I also need to ensure that I don't make a page too long and thus end up with an unwanted automatic page break from Excel. This is why I need to know how many points I can fit on each page in a worksheet.
Hopefully that's clearer, but if not, let me know.
Hey,
Still thinking about this one
I think I'm a little confused...When you say you want to know how much you can fit onto a page from a worksheet you mean point-for-point would be 100% scale from the worksheet...right? I'm also not sure how you would determine what's page 1, 2...if those pages are vertically or horizontally set. Could you provide a sample workbook of what you don't want to happen and what you do? Sorry if that seems like a lot to ask for, I'm just not too familiar with this section of Excel
And if you're worried about excel inserting page breaks into the worksheet, then you can set them yourself with code. I'm not so familiar with this, but check out:
Worksheet.ResetAllPageBreaks method
Worksheet.HPageBreaks collection (and properties and methods)
Worksheet.VPageBreaks collection (and properties and methods)
If you didn't already know
New to the forum? Check out our Introductions section to get to know some of the members here. Feel free to tell us a little about yourself as well.
Hopefully someone else more knowledgable in this area will chime in...
New to the forum? Check out our Introductions section to get to know some of the members here. Feel free to tell us a little about yourself as well.
Basically, I just need to know the size of a page in a blank workbook.
For example, if you open up Excel and make the page breaks visible, it is possible to vertically extend one of the rows a bit more to maximize usage of the page. For example, for a basic workbook, the pagebreaks occur every 52nd row and this represents a total of 663 points. However, it's possible to vertically extend one of the rows to get a total of 667.5 points per page.
What I'm ultimately in search of is a formulaor function that will give me the maximum amount of points that can fit on a page, considering the top and bottom margins. Also, this formula is just for a blank workbook with uniformly-sized rows. I need this formula so I can figure out how much I can put on a page to ensure that I'm maximizing my available space.
Well...you can fit any amount of points onto a page...if the points you are talking about are the worksheet's points. Because when you set the breaks yourself, the printout should readjust itself to fit where the breaks are located. So you could literally print the entire worksheet onto 1 page if you wanted.
That said...what do you mean by "maximize usage of a page"? If the zoom and margin settings will/may be altered anyway? (as stated earlier).
New to the forum? Check out our Introductions section to get to know some of the members here. Feel free to tell us a little about yourself as well.
Well, my issue is that I don't want to exceed the maximum number of points that can exist on a page. So if a page can hold 660 points and I put a page break at 700 points, then I will end up with 2 pages. (One with 660 and one with the remaining 40). This is the kind of situation I am trying to avoid.
Let me explain exactly how my VBA macro works. My data is a set of blocks that span multiple rows and I want to ensure that each block fits on its own page and is not split across multiple pages. So, I go through each row in the block, summing the height of all of the rows in the data block, and then determine how many blocks I can fit on a page. Then, I place page breaks between the blocks, trying to maximize my space by ensuring I am placing as many blocks onto each page as possible.
This is why I need to know how many points can fit on a page. Also, when the margin and zoom settings are changed, the maximum number of points per page change. This is why I can't just hardcode the 667.5 value in.
Hopefully this makes more sense. I'm not too clear on your suggestion that it is possible to print out the entire worksheet on 1 page by placing a page break. If you could elaborate on that too, I'd appreciate it.
Thanks for all of your help!
This can be accomplished in the page setup too as Joseph suggests..the size of the sheet can vary and still be 1 page.I'm not too clear on your suggestion that it is possible to print out the entire worksheet on 1 page by placing a page break. If you could elaborate on that too, I'd appreciate it.
pagesetup...page tab...click on the fit to 1 page radio button...
see attached and click on print preview
Steve
"Nearly all men can stand adversity, but if you want to test a man's character, give him power."
-Abraham Lincoln
That would work in some cases, but the problem is I have multiple worksheets and they all need to maintain the same zoom settings. The worksheets can range from under a page to over 10 for normal page settings.
Unfortunately, manually setting the page breaks seems to be the best option for maintaning a consistent appearance across a given workbook. So, if anyone has any further ideas on figuring out a page size, I'd appreciate it.
You can print out Page 1 (with say 58 lines) of a 200 page report at 100% zoom, aternatively you can say print to 1 page and it ALL comes out on 1 page, (you can't read it though, unless you've got REALLY good eyesight !) I have always found that someone who is concerned about where the page breaks are either isn't setting the sheet up correctly or they should be using word instead of excel (I have known accountants write memo's in excel).
2+2=9 ... (My Arithmetic Is Mental)
I don't see anything here that can't be achieved by having the page breaks visible as you enter data. Or am I missing something?Originally Posted by bbk5
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
Well, we have a fixed zoom setting that we use for these documents to keep them as standardized as possible.
Also, having the page breaks visible while entering the data doesn't really help here as the data entry is automated through a macro.
My concern is that if I overestimate how much I can fit on a page in the macro, I end up with two page breaks (one automatic and my manual one). These are the situations I am trying to avoid.
Posting Permissions
Reply With Quote
