Determining a hidden row's row height

If you set a row to have a large row height (say 80) and check the '.RowHeight' property but you then hide it the rowheight will be 0 (as expected). If you then unhide the row the rowheight will return to 80.

My question is: how ? Were does Excel store this per row value ?

It is a stupid question for someone with some much Excel dev experience but I have never before needed to find the 'unhidden' height of a row when it was hide mode was 'True' without having access to the row before it has been hidden so I could record the height.

Any pointers would be appreciated.
Who is Participating?
I don't know where the values are stored, but you can easily access the height as demonstrated in the following code:
Sub Demo()
    Application.ScreenUpdating = False
    Rows("12:12").EntireRow.Hidden = False
    MsgBox Rows("12:12").RowHeight
    Rows("12:12").EntireRow.Hidden = True
    Application.ScreenUpdating = True
End Sub

Open in new window

AL_XResearchAuthor Commented:
Thanks 'Makroshadow' for the quick response.  

That it's of course the first idea that came to mind (as long as screenUpdating is set to False so the user doesn't see it) but I want to know how excel stores these row heights.

That will allow me to access the values directly without creating a helper function (like you suggest) and create a user defined type to store the values for multiple rows.

the value is stored in the xml inside the xlsx file

change you xlsx file to zip to unzip it choose xl / worksheets / your sheet

the row will be like it

<row hidden="1" r="1" x14ac:dyDescent="0.2" ht="20.25" spans="1:8">

Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

AL_XResearchAuthor Commented:
Thanks 'Rgonzo1971', of course I know all the file contents is held in the achieve's XML, binary and resource files, although i hadn't thought of looking there. I don't think you can access the zipped XML whilst the file is open - can you ?

What I am looking for is some application / document object model property that the VBA can access.

To clarify my requirement: I need to be able to find the roiwheight value of multiple hidden rows - preferably without writing a UDF, user defined type or showing & hiding each row just to get the property (to make the voice that uses those values as fast as possible).
AL_XResearchAuthor Commented:
No one know of any VBA property of trick that would allow VBA to read a hidden row's height in VBA
(other than those already discussed) ?
I don't think so.
AL_XResearchAuthor Commented:
This didn't actually answer the question of how to determine the height whilst still hidden but is the best alternative and serves as a good example for other users who are learning VBA.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.