locked
Excel Row Heights in Fixed Increments RRS feed

  • Question

  • I note that row heights can only be set in fixed increments.
    In my testing this is 0.75 points (i.e. row heights can only be set to 0.75, 1.5, 2.25, 3, etc.).
    I have seen it suggested (on another forum) that other increments can apply and that this is tied to the default font size.
    I can find no mention of this or the fixing of increments in Excel help.

    While the above is true, Excel accepts other heights and reports them as being set (though in fact they are not).
    I have confirmed the actual height using VBA (to report .Height versus .RowHeight).
    [Interestingly, VBA .Height and .RowHeight report different values for the same cell...?]

    Any comments that shed further light on this  would be appreciated.

    As an aside, I think that mentioning this little detail in help would be useful.
    I also think that allowing Excel to display incorrect row heights is a bug (but that's just me).

    Sunday, February 19, 2012 5:30 AM

Answers

  • Although help, and the Row Height Dialog, say to set the row height in points, Excel actually sets the row height in pixels.

    1 pixel = 72 / 96 points= 0.75 points.

    That is why you can only change the row height in increments of 0.75 points.

    I agree that this is a bug.

    You can easily see what is happening if you set the height of a row to 11 points using the Row Height Dialog, and then check the row height by manually clicking the row delimiter.

        

    The nearest pixel value to 11 points is 14 pixels, so Excel sets the row height to 14 pixels = 10.5 points.


    Ed Ferrero
    www.edferrero.com

    • Marked as answer by Spinner3001 Tuesday, February 21, 2012 11:53 AM
    Monday, February 20, 2012 1:12 AM
    Answerer

All replies

  • Although help, and the Row Height Dialog, say to set the row height in points, Excel actually sets the row height in pixels.

    1 pixel = 72 / 96 points= 0.75 points.

    That is why you can only change the row height in increments of 0.75 points.

    I agree that this is a bug.

    You can easily see what is happening if you set the height of a row to 11 points using the Row Height Dialog, and then check the row height by manually clicking the row delimiter.

        

    The nearest pixel value to 11 points is 14 pixels, so Excel sets the row height to 14 pixels = 10.5 points.


    Ed Ferrero
    www.edferrero.com

    • Marked as answer by Spinner3001 Tuesday, February 21, 2012 11:53 AM
    Monday, February 20, 2012 1:12 AM
    Answerer
  • Thanks Ed,

    From the little gem of info you provided, I assume it is always 0.75 (and can't be anything else).
    That being true means the info found on the other forum I mentioned is incorrect. There it suggested 60 points was a possibility (though didn’t specify the whyfor’s).

    Any comments…?
    • Edited by Spinner3001 Tuesday, February 21, 2012 11:10 AM
    Tuesday, February 21, 2012 11:09 AM
  • Yes well, don't believe in everything you read in forums :)

    60 points = 80 pixels, so that should work.

    BTW, some systems do not have 96 pixels per inch. (Apples tend to be different).

    Whilst there will always be, by definition, 72 points per inch.

    So, on a system with a screen res of 98 pixels per inch, 1 pixel = 72 / 98 points = 0.7345 points. It follows that we would only be able to change row heights in that strange increment. This would be interesting to test on an iMac (not by me, I think I'm allergic).


    Ed Ferrero
    www.edferrero.com

    Wednesday, February 22, 2012 12:02 AM
    Answerer
  • LOL... Have you tried anti-macamines for your allergy...?

    Thanks for the additional info. I made a typo in my last… should have read 0.60 points (though your response still stands and this point is more of an academic nature). If you are interested…
    The Question posed was:
    “I am running Excel 97 on two computers. On one the row height can be increased in increments of 0.75 whilst on the other the increments are 0.60”
    The Response provided was:
    “Row height is relative to the default font (point) size. Go to Tools ¦ Options ¦ General Tab ¦ and change the default font size. Close Excel and then reopen it - and your row height increment should change. Also make sure that you are using the same Standard Font on that tab.”

    When I read the above (and assumed it may be well-informed), I changed font size but didn’t see a change in increments. I assumed it might be a combo of font and size and wasn’t going to test every permutation.
    From your info, I am glad I didn’t. Take care… and thanks again.

    Wednesday, February 22, 2012 5:00 AM
  • Hi all,

    I wrote a simple procedure for this:

    Sub ChangeRowHeight()
    
        Dim sht As Worksheet
        Set sht = ThisWorkbook.Worksheets(1)
        
        Dim rng As Range
        Set rng = sht.Range("A15")
        
        rng.RowHeight = 20.8
        
        MsgBox rng.RowHeight
        MsgBox rng.Height
        
    End Sub

    After running, we can see:

    1. Excel accepts the value 20.75 as its row height, which is not multiple of 0.75. On some further test, we can also tell  that Excel could take any number from 0 to 409 as its row height, but Excel would adjust the value slightly to make it as multiple of 0.25. However when we try to change row height via holding left button of mouse and dragging, we can see that Excel would prefer to change it in pixel by pixel, which is 0.75. So here is the result: Excel doesn't change row height by pixel, But the UI looks like it does.

    2. rng.RowHeight return 20.75, however, rng.Height returns 20.25, this looks like a bug of Excel. But we only can confirm after I get some information from some senior engineers, who can tell us how exactly this works and whether it's a bug.

    I hope my information help you.

    Good day,


    Calvin Gao[MSFT]
    MSDN Community Support | Feedback to us

    Wednesday, February 22, 2012 6:51 AM
  • Hi Calvin,
    Thank you for the reply.

    Interesting results… I did a range of test and found (at least on my system) that row heights could only be set in increments of  0.75.

    I wrote the following simple code to set row heights in increments of 0.25 points (from cells A2 to A99):

    'Set row heights from cell A2 to A99 in increments of 0.25 points
    Sub SetRowHgt25()
       Dim in1 As Integer, db1 As Double
       With Range("A1"): For in1 = 1 To 98: db1 = db1 + 0.25: .Offset(in1).RowHeight = db1: Next in1: End With
    End Sub

    I wrote the following simple function to report either .Height or .Rowheight:

    'Display .Heights or .RowHeights.
    'Suggest using per:
    '   Insert function into Cells A102 as =ReportRH(A2,1)
    '   Insert function into Cells B102 as =ReportRH(A2,2)
    ' Copy both down to cells A199 and B199 repectively...
    Function ReportRH(rgRg As Range, inType As Integer) As Double: Application.Volatile
       Select Case inType: Case 1: ReportRH = rgRg.Height: Case 2: ReportRH = rgRg.RowHeight: End Select
    End Function

    We can see that any .RowHeight value that is not an increment of 0.75 results in a rounding to the closest 0.75 increment.

    Surprisingly, when looking the results back from my ReportRH, rgRg.Height is reported accurately whereas rgRg.RowHeight reports what was "set", but not the actual height.

    If you take a close look, you will that the 0.25 and 0.5 “RowHeight” are actually hidden (i.e. confirming a row height of zero).
    NB: The same results are achieved my manually seting row heights.

    Any clues or more insights appreciated.

    Thank you again.



    • Edited by Spinner3001 Wednesday, February 22, 2012 8:33 PM
    Wednesday, February 22, 2012 10:05 AM
  • Hi Again Calvin,
    Ummm... I tested your code... and well... how can I put this... it simply supports what I said in the first place.

    Your code attempts to set a row height of 2.8 points. The message box says it was set to .RowHeight of 20.75 and .Height of 20.25.
    20.25 is an increment of 0.75... And the .RowHeight result of 20.75 is erroneous.

    As a cross-check of the .RowHeight, I suggest you try the following:
    1. Insert a rectangle shape.
    2. Turn on Snap to grid.
    3. Re-size your rectangle to the cell
    Write some simple code to read the .Height of you shape. You will find it is 20.25.
    So… either both cells and shape heights are reporting wrong or .RowHeight is.

    From my testing a .RowHeight of 0.25 results in a hidden row (i.e. one can’t step into it).
    So… I suggest .Height is correct and .RowHeight is incorrect (not the other way around) .

    • Proposed as answer by Harry Soren Tuesday, January 31, 2017 9:07 AM
    • Unproposed as answer by Harry Soren Tuesday, January 31, 2017 9:08 AM
    • Proposed as answer by Harry Soren Tuesday, January 31, 2017 9:08 AM
    Wednesday, February 22, 2012 10:36 AM
  • Not only are the increments .75 but there are Three of .75 then a 1.5 this pattern repeated but erratically

    Try along lines as the mouse move  Y value

     With wcobo
     ' an approximate linear function .. gets it right most of the time for  fonts 12 .. 24
      RH = 1.222 * .Font.Size - 0.422
      
               Idx = Int(Y / RH + 0.95) + .TopIndex
        End With


    farmer

    Tuesday, January 31, 2017 9:15 AM