Event trigger via change in row height

see attached file
As you can see from the 2 images below, the value in cell E1 changes from 37.8 to zero when my data filter is applied.
The formula in E1 is simply = A2
So the value in A2 changes when I apply a filter to my data and it returns to the original value when the filter is removed.
(and E1 merely reflects that)
Cell E1 is incidental here - it's only there to display the value in A2 when the row is filtered.
The value in B2 is the value of A2 frozen at a point in time (created with copy & paste values)

FilteredUnFiltered.jpg

The value in A2 reflects the row's current height

I require the value in B2 to always reflect the current value of A2 in its unfiltered state, whether the row is filtered or not
And what is more important - I need to access the correct current value when the row itself is filtered
When the user changes a row's height, the historic value in B2 is no longer the one I want - it needs updating at that point.

My quandry:
Due to it being an automated change in the value in cell A2, this VBA is not triggered

Please Login or Register  to view this content.

And (in any case) Target.value would be the new value (=0) after filtering which is the very value I do not want!


So the puzzle is how to update cells in column B automatically when
the user either increases or decreases the row height
without it also being triggered at the point when the row is hidden by filter
(multiple rows may be altered at the same time)

thank you

Hi,

You might use a UDF for the row height such as

Please Login or Register  to view this content.

Thanks @xlnitwit
That's a tidy way to get at the values I want
- and will be automatically triggered whenever any cell value is manually edited or a recalc forced.
- we are 50% of the way there

The other 50%
- if a user now selects 100 rows and amends their height, the values provided by that function will not be refreshed until either a recalc is forced or at least one cell is edited (any cell will do)
- if any of those rows are now immediately hidden via data filter, the values that I require are no longer accurate

Is there a way to force a recalc (to update the values) triggered at time of row height change?

Solution

1. Put xlnitwits code (per post#2) in a module

2. Put this formula in every row

Formula:

Please Login or Register  to view this content.

By now all values have been updated EXCEPT those where row heights amended after last cell edit

3. Next force a recalculation when data filters (here in row1) are amended

Please Login or Register  to view this content.

By now all values have been updated UNLESS the user has not changed the data filter

4. Finally force a recalculation when worksheet is deactivated

Please Login or Register  to view this content.

By now ALL VALUES are correctly UPDATED

Observation
Although it does not trigger when the row height itself is amended it gets the values correct by the time I need to use them which is in another sheet