How to delete hidden rows and column fast

[Xavierva]

Hello,
Currently when I filter rows and remove hidden text, I use the document inspector to 'remove hidden rows and columns'.
Some of my sheets have thousands of rows I am trying to remove, and it takes a painstaking amount of time to remove and freezes my excel temporarily (5-10min).
Does anyone know any faster or more efficient ways at removing whole rows faster?

thanks everyone for the help

[HansV]

Welcome to Eileen's Lounge!

Would it be possible to sort the sheet so that the hidden rows are all together, for example at the top or bottom of the used area? That would make deleting them much faster.
It might also help to temporarily set Calculation Options to Manual, so that formulas don't get recalculated until all hidden rows have been deleted.

[Toranaga]

Hi,

If I understand well, if you need only visible row you can copy only that rows.

[p45cal]

Posted also at https://www.excelforum.com/excel-progra ... -fast.html

[p45cal]

One advantage of doing this with Document Inspector is that it covers the entire workbook.
If, on the other hand you have specific areas on each sheet (or only some sheets?) that you want to eliminate hidden rows from it's probably faster to have a dedicated macro to delete those rows.
I tried an experiment with 8 sheets and a 5000 row range on each of them filtered so that about half of the rows were hidden. It took several minutes using Document Inspector to delete the hidden rows, whereas a small macro took 30 seconds. The macro, instead of deleting rows one at a time created a range of rows to be deleted then deleted the range in one go.

So it all depends on your specific requirements; are we talking of limited ranges on each sheet or a blanket deletion of all rows wherever they may be in the workbook?