Row / page height frustrations

[JancoW]

I'm at my wits end.

Given a certain row height, I'm trying to calculate how many rows I can fit on a printed page, and Excel is not giving me the answers I'm looking for.

Assume the following:
Page is set to A4 size (210mm x 297mm)
Top & bottom margins are bot set to 2.5cm
Row height is 12.75
The font of the normal style is set to Arial 10 pt.

When viewing in page break preview mode, I can fit 54 rows of height 12.75 on the page, and I can resize row 55 to max 22.75 pts before Excel inserts a page break.

Using the following code, I can determine that the space available to me between the top & bottom margins is 700.16 pts:

Application.CentimetersToPoints(29.7 - 2.5 - 2.5) '--Page height excl. margins

Now if I select rows 1 through 55, and get the Selection.Height value, Excel returns 711!

Now most people won't bat an eyelid over 11 points, but when you need things to line up precisely it's not negotiable. Also, depending on the row height of the bulk of the rows on the page, the difference between the two values also changes!

The madness doesn't here: If you change the size of the normal font style, you can resize the last row either higher or lower (it varies) before Excel throws in a page break! I know the normal style font has an influence on the column width, but surely not on the row height?

Don't let the tone of desperation fool you, I'm way past desperate :wink:

Regards,
Janco

 

[erik.van.geit]

Hi, Janco,

this is caused by something what I would call
"inbuilt-rounding"
rowheight is always set in steps of 0.75 = 1 pixel

here is a test
I do not get 711 but 701.25

Sub test()
Sheets.Add
Rows("1:55").Select
MsgBox Application.CentimetersToPoints(24.7)    '700.15748
MsgBox Selection.Height                         '701.25
Selection.RowHeight = Application.CentimetersToPoints(24.7) / Selection.Rows.Count
MsgBox Selection.Height                         '701.25 still !!!
End Sub

kind regards,
Erik

 

[RalphA]

Just saw Eric's fine post, after I wrote the following. I see that he and I agee on the 701.25 points for the 55 row, 12.75 point height.

Janco, let me throw in some hard facts, which I am interposing into your post, in bold:

Given a certain row height, I'm trying to calculate how many rows I can fit on a printed page, and Excel is not giving me the answers I'm looking for.

Assume the following:
Page is set to A4 size (210mm x 297mm)
Top & bottom margins are bot set to 2.5cm
Row height is 12.75
The font of the normal style is set to Arial 10 pt.

When viewing in page break preview mode, I can fit 54 rows of height 12.75 on the page, and I can resize row 55 to max 22.75 pts before Excel inserts a page break.
Note that, since one inch has 72 points, the 54 rows of 12.75 points = 54*12.75 = 688.5 points, or 688.5/72 = 9.5625 in., or 9.5625/0.03937 = 242.888 mm. Add your top and bottom margins of 25 mm, and you get 292.888mm, not the 297mm in height your A4 sheet actually is. As you can see, you are not using the proper number of rows of 12.75 points height to do your calculations!

Using the following code, I can determine that the space available to me between the top & bottom margins is 700.16 pts:

Application.CentimetersToPoints(29.7 - 2.5 - 2.5) '--Page height excl. margins

Now if I select rows 1 through 55, and get the Selection.Height value, Excel returns 711!

Now most people won't bat an eyelid over 11 points, but when you need things to line up precisely it's not negotiable. Also, depending on the row height of the bulk of the rows on the page, the difference between the two values also changes!

Continuing my calculations with all the decimals considered, I get your value, 700.1561 points, exactly. Hmm, seems the code is giving a wrong return value, if it is 711 points, as you report.
On the other hand, I get, for 55 rows with a height of 12.75 points, a total of 701.25 points. And, Excel is showing me 55 rows to a page, exactly, or 55*12.75 = 701.25 points, or 247.3859mm, a mite more than the 247mm that we actually have available. Perhaps Excel cheats a little, and encroaches a bit on one or both of the 25mm top and bottom margins?

In any case, I think you see my point, in that one must do very exact and extensive calculations of the row height, if one is to arrive at an exact and repeatable number of rows per page

The madness doesn't here: If you change the size of the normal font style, you can resize the last row either higher or lower (it varies) before Excel throws in a page break! I know the normal style font has an influence on the column width, but surely not on the row height?
Think about it! If the font size is reduced, the result can be shown in the current row height, and, no change in row height is required. But, if you increase the font beyond what Excel has decided is the maximum row height to accomodate the font size in a predetermined "nice" way, why, it will simply increase the row height to a new, "proper' height. No mystery here, really.

 

[JancoW]

Hi guys, thanks for the replies.

I don't think I made myself 100% clear though :wink:

I'm trying to get the page break as close as possible to the bottom margin. So I've actually got 54 rows of height 12.75, and then row 55 is set to 22.75. The page break then automatically falls on the bottom of row 55. If you now get the Selection.Height of rows 1-55 you should get 711. If you were to resize row 55 to the next possible value (23), the page break jumps up to row 54, so row 55 can't be higher than 22.75.

If you now draw a border at the top of row 1, and at the bottom of row 55 the borders should line up very closely with the margins on the page (you can check this on the print preview with margins turned on). What's disconcerting now is that according to the original calcs, the available space between the margins should be 700.16. So how can the Selection.Height be 711, but the available space between the margins (if calculated) is 700.16, and the rows actually fit onto the page?!

Katy, I've checked the bit about the normal style font again, and I think what happened there was that Excel automatically changed the row size when I changed the font size, which I wasn't aware of.

It's a pity I can't post an example here.

Thanks for the help so far guys!

 

[erik.van.geit]

to tired to think
these are my tests

Sub test()
'Sheets.Add
Rows("1:54").RowHeight = 12.75
Rows("55").RowHeight = 22.75
Rows("1:55").Select

MsgBox Application.CentimetersToPoints(24.7), 64, "Application.CentimetersToPoints(24.7)"
'700.15748

MsgBox Selection.Height & vbLf & "(12.75 x 54) + 22.75 = " & (12.75 * 54) + 22.75, 64, "Selection.Height"
'711 '711.25

'one point is equal to 0,035 centimeter
MsgBox Selection.Height * 0.035 & vbLf & "should be 24.7", 64, "Selection.Height * 0.035"
'24.885
End Sub

Sub test()
Sheets.Add

    With ActiveSheet.PageSetup
        .TopMargin = 0
        .BottomMargin = 0
        .HeaderMargin = 0
        .FooterMargin = 0
        .PaperSize = xlPaperA4
    End With

    With Rows("1:50")
    .RowHeight = Application.CentimetersToPoints(29.7) / .Rows.Count
    .Select
    .Resize(.Rows.Count + 1, 1) = "123"
    End With

ActiveWindow.View = xlPageBreakPreview
Set ActiveSheet.HPageBreaks(1).Location = ActiveSheet.Range("A51")
ActiveWindow.View = xlNormalView

MsgBox Selection.Height & vbLf & "Rows(1).RowHeight * 50 = " & Rows(1).RowHeight * 50, 64, "Selection.Height"
'850

End Sub

don't know what to do with this anymore

thinking at "fit to page" which solves the problem anyway

or ??..

 

[RalphA]

Janco:

54 rows with 12.75 points height = 688.5 points
1 row with 22.75 points height = 22.75 points
The above adds to 711.25 points

One inch has 72 points and measures 1/0.03937 mm, exactly.
Your available space of 247 mm would have 247[mm]*0.03937[in/mm]*72[points/in] = 700.1561 points, which is over 11 points smaller than the height of your 55 rows. What gives?

For instance, for 55 rows of 12.75 points each, we get 701.25 points, or 701.25[points]/(72[points/in]*0.03937[in/mm]) = 247.38591144 mm, which is not close to being an exact number of mm. We could certainly approach this value for your A4 sheet by using one of the two margins just a mite smaller than the 25 mm you are currently using, that is, by using 24.61408856 mm for one of them. But, how close can you actually get to that value? What is the minimum increment that you can get with Excel for margins in mm?

The basic problem you are facing is that Excel’s spreadsheet is dimensioned in the American “English” system, which Congress declared, back in the seventeen hundreds, that the legal system of measurment for length, for the United States of America, is the meter, and defined it as containing 39.37 inches. From that moment on, there is no practical way of translating American inches to the metric system in an exact way. So, you must play with the row height, expressed in points, with one inch = 72 points, and, as Eric has explained, the row heights incremented by 0.75 points (giving, for example, rows of height 11.25, 12.00, 12.75, 13.50, and so on).



If you still think that we may yet help you, please do not hesitate to post again. One possible approach would be to have more than one row of different heights than the basic one chosen, but, even so, the closest I got to 247 mm with various trys is 247.1213 mm, using 54 rows w/ 12.75 points, and 1 row w/ 12.00 points. But, this will not give an indefinite number of sheets with 55 rows. As I explained, i would seem impossible to get an "exact" fit for the metric system