Macro for Displaying Row Height and Column Width

[bkanne]

Could someone please help me write a user defined function (i.e. ROWHEIGHT(A1) or COLUMNWIDTH(A1)) that displays the row height and column width of the actively selected range?

If a single cell was selected and the code was run, it would display "8.333" (as an example), and if multiple cells were selected, it would display the sum of those row heights (or column widths).

I've attached an example spreadsheet to help better explain. Thank you for any guidance or help!

 

[SirJB7]

Hi, bkanne!

This code does the job:

Option Explicit

Public Function lColumnWidth(pRange As Range) As Long
    Dim I As Long, L As Long
    L = 0
    With pRange
        For I = 1 To .Columns.Count
            L = L + .Columns(I).Width
        Next I
    End With
    lColumnWidth = L
End Function

Public Function lRowHeight(pRange As Range) As Long
    Dim I As Long, L As Long
    L = 0
    With pRange
        For I = 1 To .Rows.Count
            L = L + .Rows(I).Height
        Next I
    End With
    lRowHeight = L
End Function

But...

You'll get only one of the figures that Excel shows when changing row heights or column widths.

Check these links for further information on how Excel measures rows and columns and what units does it handles:
https://support.microsoft.com/en-us...-of-how-column-widths-are-determined-in-excel
https://www.pcreview.co.uk/threads/how-row-heights-are-determined-in-excel.1016898/

Regards!

 

[bkanne]

This is fantastic thank you. I am a bit confused as to what units are being returned by the formula...I understand that it's not in pixels/characters (i.e. doesn't match the row height of 15 or column width of 12 in the spreadsheet), but what is the unit of measure?

Thanks again!

 

[bkanne]

This is fantastic thank you. I am a bit confused as to what units are being returned by the formula...I understand that it's not in pixels/characters (i.e. doesn't match the row height of 15 or column width of 12 in the spreadsheet), but what is the unit of measure?

Thanks again!

I also don't fully understand why I'm getting the same value for each function...even after reading the other links that you referenced. I would think that one would show the row height and one would show the column width...and given that I don't understand what the unit of measure is, I have no idea which is being returned by either formula.

 

[SirJB7]

Hi, bkanne!
It isn't so easy to answer that easy question. That's why I included those introductory links as a guidelines for future reading.
Regards!

 

[SirJB7]

Hi, bkanne!
My mistake, a typo error: in the 2nd function where it says ".Width" it must say ".Height".
Regards!

 

[bkanne]

Great, thanks for the clarification. Now it returns the Row Height as I expected it would...wonder if there is a way to figure out the column width issue to return a similar answer.

Appreciate the help very much!

 

[SirJB7]

Hi, bkanne!

That would be great, if only Microsoft had stated that width & height were measured in pixels, but they didn't. So I'm afraid there's no much to do than choosing between taking it or leaving it.

If you go thru the link's documentation you'd surely understand the main issue but don't expect to find a workaround. If you do, please share it with the community.

In a very old thread I had to deal with this issue. Just FYI:
http://chandoo.org/forum/threads/anyone-attempted-to-create-this-using-excel.2856/

Regards!