Original height of hidden row

Add-in Express™ Support Service
That's what is more important than anything else

Original height of hidden row
 
Pavel


Guest


Hello,

When one sets a filter on table in excel, the height of the rows that are matching the filter criteria is set to zero.
If user clears the filter, the height of each row is then restored to its original size.

I was wondering if there is a programatic way to determine the original height of currently hidden row of a listobject ?


BR

Pavel
Posted 04 Jun, 2018 05:59:53 Top
Andrei Smolin


Add-in Express team


Posts: 18821
 Joined: 2006-05-11
Hello Pavel,

The Excel object model seemingly doesn't provide such a property; at least, I can't find such a property. I'd look for a way to get the height of that row(s) before the filter is applied. As a reserve way, I would use the CommandbarsUpdate event to compare the current height with the previous value; note that this event may fire really often and your code must be effective to prevent slowing Excel down.


Andrei Smolin
Add-in Express Team Leader
Posted 04 Jun, 2018 07:09:49 Top
Pavel


Guest


Hello Andrei,

Thanks for clarification.

I dare to have one more question regarding the Autofilter , if possible :-):

Is there an easy way how to show all items in a listObject that's has the autofilter on and then apply the filter back without loosing the autofilter's criteria.

I mean I know that I can turn off the autofilter off, serialize all the filter settings and then recreate the autofilter . But it seems to mee that is is quite an extra work for something that might be relatively easy. As well it would add quite some complexity to the code of my addin.


BR

Pavel
Posted 05 Jun, 2018 07:19:28 Top
Andrei Smolin


Add-in Express team


Posts: 18821
 Joined: 2006-05-11
Pavel,

You can use Range.SpecialCells(XlCellType.xlCellTypeVisible) to get visible cells of a range. Then you can compare the visible range with the whole range.

Also have a look at the example published at https://msdn.microsoft.com/en-us/vba/excel-vba/articles/hide-and-unhide-columns. In the example, they hide and unhide columns; you can do the same with rows.


Andrei Smolin
Add-in Express Team Leader
Posted 05 Jun, 2018 08:30:47 Top