Determine Excel Row Height without VB code 2

[JVFriederick]

There is a fomula within XL to determine column width
=CELL("Width",A2). I like to place this formula in row 1 for every column used within each sheet.

This is handy when I wish to keep various worksheets looking the same from sheet to sheet or file to file.

I have searched but have not found how to write a formula to determine the row height. I can do this with VB, but I wanted a simple formula for this task.

Does anyone know how to do this without VB ?

 

[KenWright]

You cannot do it without VBA, and even what you are doing now does not ensure you get a standard width as the value that is returned by the CELL function is rounded off to the nearest integer anyway, so 8.86 for example will be shown as 9, or 8.43 will be shown as 8.

What i have is a piece of code that lets me select an area (usually a table), determines the max width of the column of the widest entry, and then applies that width to all the columns in the selection, giving me exactly what it sounds like you are after.

This isn't the VBA forum, but just in case it is of any use:-

Sub FixCols()

Dim y  As Double
Dim c  As Range
    y = 0
    With Selection
        .ColumnWidth = 1
        .Columns.AutoFit
        For Each c In .Columns
            If c.ColumnWidth > y Then
                y = c.ColumnWidth
            End If
        Next
        .ColumnWidth = y
    End With
Application.ScreenUpdating = True

End Sub

Regards
Ken...........

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[DaleWatson123321]

Jim,

For me, the easiest method of creating sheets with IDENTICAL row-heights and column-widths...

1) If different data already exists on the "destination" sheet (the one to be made the same as the "template" sheet, then copy the data to a separate (temporary) sheet - for later copying back.

2) On the "template" sheet, select the entire sheet - by clicking on the top-left-corner of the sheet - i.e. to the left of "A" and above "1". (I don't know if this has a name, so let's refer to it as the "sheet selector".)

3) Copy - with either <Ctrl> c - or right-click, copy

4) Click on the tab of your "destination" sheet.

5) Click on the "sheet selector".

6) Paste - with either <Ctrl> v - or right-click, paste

7) Click on the tab or your temporary sheet (where you copied your data in step 1 (if you had data to copy)

8) Copy the data only - i.e. do NOT use the "sheet selector". Rather, use these steps:
a) Click on A1,
b) While holding down the <Shift> key, hit the <End> key, followed by the <Home> key. This will highlight all your data.
c) Copy - with either <Ctrl> c - or right-click, copy

9) Click on the sheet tab of your destination sheet.

10) Click on A1

11) Paste - with either <Ctrl> v - or right-click, paste

Jim, I know you've got plenty of experience and don't require the detailed steps; however I've provided the detail for those other Tek-Tips users who will perhaps appreciate having the detail.

I hope this proves useful.

Regards, Dale Watson

 

[JVFriederick]

Ken - thanks for the reminder, you are absolutely correct about rounding on the cell width function. As it happens, I always set the column width manually to an integer (which may stem from the days when a ruler was used to lay out a report......) so I am OK with the XL function.

I am unhappy to hear there there is no function for the row height. The basic need came from doing database queries, and wanting the row height to be consistant vertically.

I have found a work-around though. If I set the font on a cell to say size 12, it will automatically set the row height to 15 for all the records returned. I can use a column that needs to be emphasized, or add a column and then hide it. Not an elegant solution since I do not have perfect control, but usually a row height of 15 looks good on a report.

Dale - it there was a Sheet Group setting like in Lotus we wouldn't be having that problem, but that's another story for another day !!!!

Thanks.