Auto hide blank rows

Workbook attached

I'm trying to build a products database, which does not need to be very sophisticated to be honest. I have separate sheets for separate sub categories and i want to update this as new products arise, no problem. Occasionally I am required to provide a product index of specific products supplied on a specific project.

Before today, i had all of my 'sub categories' on one sheet from top to bottom and would simply hide all the rows that were either blank or did not contain a product that was used on a job, i would then print the remaining rows to create the bespoke project index before 'un-hiding' the rows. This has proved rather long winded.

What i would like to do, if its even possible, is work my way through the sub categories sheets marking column E with an "x" or tick or "yes" if the product is to be included in the print copy and then once i've worked through all the sub categories sheets 'ticking' or marking all the appropriate products i can 'activate' the print copy sheet and it will auto populate the rows/ hide all unnecessary rows etc leaving me with a full list or index of only the products actually used. where this might get even trickier is that i need labelled 'dividing' rows between sub categories (shown in the workbook).

I've begun working on this and tried to do this using some code (in the O&M print copy sheet) but it doesnt bunch up the data and leaves blank rows plus i don't know how to adapt the ranges either side of my 'dividing rows'.

Am i missing something? or is this simply not possible to achieve?

Your thoughts or advice is greatly recieved. xProduct index Master Forum example.xlsm

Put this code on your worksheet:

Please Login or Register  to view this content.

Put this code in a module:

Please Login or Register  to view this content.

ON the worksheet you're "ticking" off on, you'll need some sort of counter, like =Countif(Y:Y,"X"). In your "Z" column (you may need to adjust these), you'll need to determine "Unhide" range and "Hide" range. Unhide can be a static total range of your worksheet (like A1:A100). Your hide range will be dynamic, like ="A"&Z4&":A100" where Z4 = your count of x's + however many rows you need for headers.

Hope this makes sense. I just built something VERY similar.