Hello, is there a way to permanently hide rows in Excel when data filtering is enabled? If I manually hide unwanted rows and then change the data filter criteria, the manually hidden rows will become visible again (for those rows which satisfy the filter criteria). The rows I want to hide don't follow any specific filter criteria so I can't filter them out of view. So I need a way to keep both manual and data filter hidden rows hidden. Tips and tricks welcome, thanks!
Cuz' that data might be relevant later. Not to mention filtered work sheets tend to break if you move or delete cells... and deleting only values would leave blank rows which isn't really any better. As a workaround I Grouped the rows in question so I can hide (collapse) them with a single click. They reappear every time I change filter criteria but at least I needn't manually hide dozens of rows from hundreds of rows of data. Exporting BOMs from PDM to Excel to try and create a BOM-by-assembly-operation is fun! Not
What about a pivot table? Only criteria fitting the filter shows and the actual data table is located out of site.
Would have the same problem still, the (manually) hidden rows have various criteria so they cannot be filtered out. A pivot table needs criteria for data input/output. Also the hidden rows are located randomly within the data area so they cannot be isolated by giving a row/column range either. Ie. the hiding must be done manually, I just want to make it stick when filtering is changed. The Grouping method pretty much achieved what I wanted, still need to click the collapse button if filtering is changed but one click is acceptable. Plus now it's easy to re-access the hidden data if needed by expanding the Groups. But thanks for the idea and if anyone finds out a way to perma-hide cells then please share.
I think only solution to your request, would be to attach VBA script after you hit filter action, so when calculate event triggers after that, it would hide all your previously hidden rows again.