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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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">

Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.