Underlying row height

For once, I respectfully ask that you do not reply to this thread unless you have a definitive YES or NO answer
- some members do not look at threads which already contain replies - and I would prefer this thread to remain unanswered until someone can give me a categoric YES or NO
I have a very specific need. I am not looking for a workaround. Removing and re-applying the filter is not an option - the filter is complex (and frequently changing!) and applied manually - capturing those values in VBA to re-apply the data filter each time would require an impressive bit of VBA coding - and I do not want to go down that route.
thank you


With data filter applied .RowHeight returns value = 0 in respect of any rows filtered out
With data filter turned off, the row returns to its previous height - Excel therefore holds all the original values somewhere

Can VBA return that original value at the moment when the row is filtered out (whilst the workbook is open)?
(And if so what is the code)

The last line of your signature with a smily encouraged me to ask for an explanation of what you want.

@ImranBhatti How totally unhelpful of you

Hi kev_

If I understand your question correctly then this could help... Searches range for hidden row and returns row No and Row Height

Please Login or Register  to view this content.

@Sintek - no you did not understand the question

This is an example of what I am trying to achieve:
Format a row and set its height to 100
- using VBA .Height and .RowHeight both tell us the height is 100
Now hide it (or filter it out)
- using VBA .Height and .RowHeight both tell us the height is now 0
Unhide the row
- using VBA gives the the height = 100 again
- so Excel knows how to re-set the height after its been hidden - Excel does NOT require to be told the 100 - Excel REMEMBERS that value

What I want is for VBA to tell me the number 100 whilst the row is still hidden
- it is a waste of time trying to use either .Height or .RowHeight

Now you've got me wondering as well. My apologies for not understanding. Glad you clarified. Will be searching the web.

I would prefer this thread to remain unanswered until someone can give me a categoric YES or NO

I cannot provide a categoric yes/no answer myself, but, since it seems to have gone a little cold, I will proffer this observation and see if it helps get to the categoric yes/no.

As many know, since at least 2007, xlsx (and other Excel files) are really "zipped" files containing a bunch of xml files. I created a small, simple spreadsheet with a bunch of random numbers, changed the height of a few rows, and filtered them to hide column A values greater than 0.5 then saved and closed. Renamed the file to change the extension from xlsx to zip and unzipped the files, found the xml file for the spreadsheet, then looked to see if "row height" is stored there. Some of the content of that xml file:

Please Login or Register  to view this content.

Row 5 is perhaps of most interest here, as you can see that it has a different row height from the default, and it also has the hidden indicator. Compare to row 8 which has a non-default row height, but is not hidden and to the other rows which are all at default row height.

I have no idea how to access that information from VBA, but I can see that the information is stored in the file and where it is stored in the file. Perhaps that will prompt something towards figuring out how to access the information (or maybe it won't because it is just too impractical to access the xml code of an open file).

Thank you Mr Shorty
I want to access this information in the live situation where the data will have been sorted - and so any row numbers previously stored in an xml would be incorrect.
So what could possibly work would be a dump to xml, extraction of all row heights from xml, immediate use of those values etc. Unfortunately if data is sorted again or row inserted we go back through the same loop.
But that seems very long-winded if you can get at the value directly.
it would be so much easier to get at the value directly.
I am having a separate play with extracting those values using

Formula:

Please Login or Register  to view this content.

as RefersTo formula in a named range "RowHeight"
And then using formula

Formula:

Please Login or Register  to view this content.

in a cell in each row
Try it - it works
- but it appears the values do not change if the row height is changed - it's almost as if the original row height value gets stuck in there.
I have tried refreshing, and saving the file again. After doing that I entered the formula in a new cell, and it still gives me the previous value - I did not expect that.
I could use VBA to recreate the formulas on the hoof to get the values if I can get the value to change using the formula in Excel in the first instance.
So the next step is to close the file and re-open it to see if that gets me anywhere!
I may start a separate thread on it to see if there is a trick I am missing - but I remember things so much better if I puzzle things out myself - so if you see the thread I will have been defeated!


EDIT1
That did not work either
In sheer desperation I tried it on a clean file - and it works beautifully - but
I am now off to see if I can break that.....

EDIT2
Unfortunately that was not quite what I wanted either -it also changes to zero when row is filtered out
Next step VBA to "freeze" value at the correct time and then recalc again when row height changes

NOTE:
I am still looking for a definitive answer to the question posed at the beginning of this thread

@thiefofthings - thank you - and I totally agree with you
This particular thread is part of a couple of other issues that I am wrestling
- I am trying to avoid any complicated code (or, indeed, complications of any sort)
- I was just hoping that someone had found a simple way to get at that "secret" value
- I live in the land of eternal hope...



NOTE:
I am still looking for a definitive answer to the question posed at the beginning of this thread
Let's banish parsing XML from our minds - it seems too damn complicated..

It seems to me that creating and parsing an XML file is a more complicated work-around than simply un-hiding a row, reading its RowHeight and hiding it again.

@mikerickson - we are of one mind!

I'm curious why this needs to be discovered while the row is hidden. (Aside from abstract curiosity). The RowHeight of a cell is inherently a visual thing and one could wait until the cell is visible before adjusting it.

The purpose is to do quite the opposite to what Excel thinks we should do - it's being used to set the row height elsewhere.

Imagine the reverse of:
- advanced filter in sheet1, paste filtered results to sheet2

My mission is to filter in sheet1, paste ALL rows to sheet2 without lifting the filter first - that is why I need the "secret" row height
(the overall puzzle is solved - but I was trying to simplify things)

And yes, in principle, it would be much easier to lift the filter, grab all row heights, replace the filter
- but that assumes that the filters are not manually placed complex, cumulative, with variable sort

And before you ask .... no, I am not being obtuse .... it is not my workbook - but it's been a fascinating challenge to turn Excel on its head!

It sounds like you are trying to create a "PasteSpecial/Row Height" command, since copy/paste doesn't adjust row heights even of visible cells.

I hadn't thought it through that far, but you are quite right. Perhaps I should patent it and sell it to Microsoft

But lifting the filter and putting it back in is trivially easy isn't it?
save a custom view and then remove the filter (and do whatever you want) and put the custom view back in.

Does that not work for all of your sheet?

Thanks scottiex - I will test that.....

EDIT
I did. It worked
What an elementary case of tunnel-vision on my part!

So, to tidy everything up on this thread ....

To find the value H and leave the sheet as we found it...

Please Login or Register  to view this content.

Thanks to everyone who unwittingly followed me up this particular blind alley

@mikerickson - some bonus code for you just in case you should ever need it

Please Login or Register  to view this content.

@scottiex That's the joy of the forum - we all learn from each other