Display row height and column width in inches a cell

[Edgar_hj1]

Hello,
I want to display row height in inches in a cell (the same value displayed when using page layout mode), and i want to do the same with column width.
I am using Windows 7 and office 2010.
I found this code:

<CODE>Function Width(MyRange As Range) As Double[INDENT]Application.Volatile
Width = MyRange.ColumnWidth[/INDENT]
End Function</CODE>


</PRE>

<CODE>Function height(MyRange As Range) As Double[INDENT]Application.Volatile    
Height = MyRange.RowHeight[/INDENT]
End Function</CODE>


</PRE>

but it displays values in characters for columns and points for rows.

Can anyone help me?

Thanks

 

[Asala42]

If you go to the Page Layout view there is an inches scale where you can work out the conversion. (Set a cell to 1" by 1" and get the width/height numbers back in the normal view).

At 1", Width = 12 and Height = 72.

Unfortunately this does not appear to be linear for the Width dimension as you test out 2" by 2", 3" by 3", etc. Height does appear to be consistent at least, but the width per inch ratio appears to grow non-linearly: 1"=12, 2"=12.36, 4"=12.57, 5"=12.60, 10"=12.67. So I'm not sure what can be done there without a line fit.

 

[pgc01]

Hi Edgar

Instead of the .ColumnWidth property that gives you the width in character units, which is tricky with proportional fonts, use the .Width property, that returns the width in points.

Ex. In the page layout view set the width of column A to 1 inch and execute:

    MsgBox Range("A1").Width

You should get 72.

 

[Edgar_hj1]

If you go to the Page Layout view there is an inches scale where you can work out the conversion. (Set a cell to 1" by 1" and get the width/height numbers back in the normal view).

At 1", Width = 12 and Height = 72.

Unfortunately this does not appear to be linear for the Width dimension as you test out 2" by 2", 3" by 3", etc. Height does appear to be consistent at least, but the width per inch ratio appears to grow non-linearly: 1"=12, 2"=12.36, 4"=12.57, 5"=12.60, 10"=12.67. So I'm not sure what can be done there without a line fit.

Thanks for your help Asla42.
I tested the same way you did and i don't know how to make a non-linear formula that predict width behavior.
I found converter in a web site but they do not provide the convertion formula.

 

[pgc01]

Did you try the code I posted? It gives you the value directly.

 

[Asala42]

it tests correctly for me.

Function Width(MyRange As Range) As Double
         Application.Volatile
         Width = MyRange.Width / 72
End Function

 

[Edgar_hj1]

thanks so much pgc01, honestly, I don't know where to include your code in the function. I am not a programer.

 

[Edgar_hj1]

thanks so much Asala42