display column width and lenght

[dgrosen]

Hi everybody
Is there any formula or something to display on the first cell of each column the width of the column and on the first cell of each row the lenght of it?

Thanks
Danny

 

[Egress1]

Hi everybody
Is there any formula or something to display on the first cell of each column the width of the column and on the first cell of each row the lenght of it?

Thanks
Danny

Not sure about a formula but you can get and write that info in VBA with

CW = ActiveCell.ColumnWidth
RH = ActiveCell.RowHeight

 

[phantom1975]

Here are some user-defined functions that will do the trick:

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

Function Height(MyRange As Range) As Double
    Application.Volatile
    Height = MyRange.RowHeight
End Function

You can then do =WIDTH(A2) or =HEIGHT(C1) etc.

 

[PaddyD]

see help file for the cell() function, which has an argument for col width. oddly, it does not have a height equivalent ?!?

 

[RalphA]

Expanding on Paddy's good information, I have placed, in the first column cell of a given row, the formula =CELL("WIDTH",A1), where A refers to the first column whose width is desired. Then, I just copy that formula across the row, as far as I need, and, the column "widths" become visible.

However, be advised that you only get the rounded-up integer value of the width. Thus, if the width is 8.44, you obtain 8, and, if the width is 8.56, you obtain 9.

So, if you are needing to get exact values, the only way I know of is to put the mouse cursor at the meeting together of two consecutive column letters, say B and C, to read the width of column B. Then, write down the value read, and, so on.

Now, if you mean to format your columns so as to get a sheet that will fit on a given size paper sheet, the most practical way is to simply set your print area, then, you can see the dashed line that represents the edge of the paper. Now, you may proceed to reduce your columns as necessary to obtain the page width you need to print out.

 

[PaddyD]

caveats notwithstanding, you could also use get.cell as described here:


http://www.mrexcel.com/board2/viewtopic.php?t=21312

the relevant arguments are 17 and 44 minus 42

paddy

 

[dgrosen]

OH YEAH!!! the user defined functions worked pretty nice
Thanks to all folks
:wink:

 

[Edgar_hj1]

Here are some user-defined functions that will do the trick:

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

Function Height(MyRange As Range) As Double
    Application.Volatile
    Height = MyRange.RowHeight
End Function

You can then do =WIDTH(A2) or =HEIGHT(C1) etc.

This user-defined functions worked great for me, but i need cell width and row height displayed in inches as in the page layout view mode. Is there any way to display those values in inches as excel do it?

 

[Kjohnsonm]

This code worked for me perfectly however does anyone know how to make the new "width" macro/udf become available from the default formula items? I work at a fairly large institution, and this could save a lot of time for others and remove risk of opening excel files with macros enabled if it could just be added to my our default install package. etc (sorry if the terms I am using are not quite right) -KJ

 

[Kjohnsonm]

caveats notwithstanding, you could also use get.cell as described here:


http://www.mrexcel.com/board2/viewtopic.php?t=21312

the relevant arguments are 17 and 44 minus 42

paddy

This link is failing to open, (I am logged in to the site of course)