Auto-hide/un-hide Blank Rows

[ChuckRobert]

I have a worksheet containing 500 rows of potential data. Data validation list drop-down boxes are used to select assignments which are then referenced by Index & Match to automatically display in other parts of the worksheet as required. Up to 70% of the worksheet contains blank rows, waiting for assignments that may be infrequently assigned.

I need to auto-hide all rows in which the cells in columns D:P each contain "0" or blank "". If a new assignment is set with data validation that puts an assignment in a previously hidden row, I need that row to automatically un-hide.

I looked at Private Sub Worksheet_SelectionChange(ByVal Target As Range) to run a macro process to un-hide rows, but need to consider that many other macros reference/paste to this worksheet when processes are run.

I tried the autofilter which, works great for a particular date (filtering name & assignment - column D:E for Monday), but that hides other assignments that may be set on other days of the week.

 

[Datsmart]

I would suggest looking at Advanced Filtering to do what you are after.
Good example of Advanced Filter steps here:
http://www.contextures.com/xladvfilter01.html
AdvancedFilter Demo
http://www.contextures.com/xlVideos04.html
Another good link with sample VBA code here:
http://www.meadinkent.co.uk/xlfilter.htm

Advanced Filter allows you to preset filter options.
It also allows you to filter by criteria in ways standard autofilter can not do. You can choose an export location in the dialog box or in VBA code.
Once you setup the code and criteria, you can use a macro button to copy specific data from your main sheet to a "Report" sheet. The original data remains unchanged and the data "Filtered" to another page can be used as desired.

 

[epsukas]

Here´s easy tutorial of how to hide blank cells.

https://www.youtube.com/watch?v=SoCz4LA8eKM