Auto-hide/un-hide Blank Rows

ChuckRobert

Board Regular
Joined
Feb 26, 2009
Messages
64
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.
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

Datsmart

Well-known Member
Joined
Jun 19, 2003
Messages
7,985
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.
 

Forum statistics

Threads
1,144,568
Messages
5,725,046
Members
422,590
Latest member
Mikeyyy

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top