Skip to main content

How to display row height or column width in cells?

Author: Xiaoyang Last Modified: 2014-08-28

In some cases, you may want to know the height of each row or the width of each column in your worksheet. Are you still use the Row Height or Column Width to check the row height or column width one cell by one cell? Today, I will introduce some tricks for you to deal with this task quickly.

Display each row height in cells with Define Name

Display each row height in cells with User Defined Function

Display each column width in cells with User Defined Function


arrow blue right bubble Display each row height in cells with Define Name

In Excel, we can create a range name, and then apply its corresponding formula. Please do with following steps:

1. Activate your worksheet which you want to get the row height and column width.

2. Click Formulas > Define Name, see screenshot:

doc-row-height-1

3. Then in the New Name dialog box, enter your define name into the Name text box, and then type this formula =get.cell(17,a1) into the Refers to box, see screenshot:

doc-row-height-1

4. And then click OK button to close this dialog, now in a blank cell, please enter this formula =rowheight, see screenshot:

doc-row-height-1

5. Then drag the fill handle down to the range cells which you want to get the row height, and each row height has been filled into your cells.

doc-row-height-1


arrow blue right bubble Display each row height in cells with User Defined Function

If you are interesting in code, you can also create a User Defined Function to get the row height of each row.

1. Hold down the Alt + F11 keys in Excel, and it opens the Microsoft Visual Basic for Applications window.

2. Click Insert > Module, and paste the following macro in the Module Window.

VBA code: display each row height in cells

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

3. Then save and close this code, return to your worksheet, and enter this formula =RowHeight(A1) into any blank cell of first row, then drag the fill handle down to the range cells which you want to display the row height of each row.


arrow blue right bubble Display each column width in cells with User Defined Function

To get the column width of each column, you can also use a User Defined Function.

1. Hold down the Alt + F11 keys in Excel, and it opens the Microsoft Visual Basic for Applications window.

2. Click Insert > Module, and paste the following macro in the Module Window.

VBA code: display each column width in cells

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

3. Then save and close this code, return to your worksheet, and enter this formula =ColumnWidth(A1) into any blank cell of first column, then drag the fill handle right to the range cells which you want to display the column width of each column, and you will get the relative column width in the cell. See screenshots:

doc-row-height-5
-1
doc-row-height-6

Related article:

How to change the row height / column width alternated in Excel?

Best Office Productivity Tools

🤖 Kutools AI Aide: Revolutionize data analysis based on: Intelligent Execution   |  Generate Code  |  Create Custom Formulas  |  Analyze Data and Generate Charts  |  Invoke Kutools Functions
Popular Features: Find, Highlight or Identify Duplicates   |  Delete Blank Rows   |  Combine Columns or Cells without Losing Data   |   Round without Formula ...
Super Lookup: Multiple Criteria VLookup    Multiple Value VLookup  |   VLookup Across Multiple Sheets   |   Fuzzy Lookup ....
Advanced Drop-down List: Quickly Create Drop Down List   |  Dependent Drop Down List   |  Multi-select Drop Down List ....
Column Manager: Add a Specific Number of Columns  |  Move Columns  |  Toggle Visibility Status of Hidden Columns  |  Compare Ranges & Columns ...
Featured Features: Grid Focus   |  Design View   |   Big Formula Bar    Workbook & Sheet Manager   |  Resource Library (Auto Text)   |  Date Picker   |  Combine Worksheets   |  Encrypt/Decrypt Cells    Send Emails by List   |  Super Filter   |   Special Filter (filter bold/italic/strikethrough...) ...
Top 15 Toolsets12 Text Tools (Add Text, Remove Characters, ...)   |   50+ Chart Types (Gantt Chart, ...)   |   40+ Practical Formulas (Calculate age based on birthday, ...)   |   19 Insertion Tools (Insert QR Code, Insert Picture from Path, ...)   |   12 Conversion Tools (Numbers to Words, Currency Conversion, ...)   |   7 Merge & Split Tools (Advanced Combine Rows, Split Cells, ...)   |   ... and more

Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time.  Click Here to Get The Feature You Need The Most...

Description


Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
Comments (5)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
The column user defined function only works for one column. I edited as below to give the total for multiple columns. The same concept could be applied to the Row UDF.

Function ColumnWidth(MR As Range) As Double
Application.Volatile
Dim c As Range
For Each c In MR
ColumnWidth = c.ColumnWidth + ColumnWidth
Next c
End Function
This comment was minimized by the moderator on the site
C'est un bug Excel : une function n'est recalculée que si une "valeur" d'une des cellules d'entrée change. Or, les valeurs ne changent pas, mais juste la largeur ==> donc pas de recalcul. Même pas avec F9...
Un contournement (pas terrible mais évite de revalider chaque cellule à chaque fois) : mettre par exemple "+aujourdhui()*0" à la fin de la formule qui appelle la function, elle se recaculera dès l'appui sur F9.
This comment was minimized by the moderator on the site
These code options only seem to work "at first." When you then adjust the row height, the value does not update (Excel in Office 365). Any resolution to that? I want to display a value or use conditional formatting based on the calculated row height.
This comment was minimized by the moderator on the site
The Range Name is only working for one cell height and not the others
This comment was minimized by the moderator on the site
this works until you use the custom formula in a new tab. it references the original tab you created the formula in
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations