Worksheet Change Hide/Unhide rows automatically

I'm guessing you've just coded that in a standard module. It is not a true Worksheet Change event handler. That sits behind the Worksheet you want to monitor in the "Worksheet class module". It has a Target parameter which refers to the cell, or range of cells, that have changed. It is usual to restrict monitoring of cells to a specific range; in this case, probably column Q.

Suggest you post a sample workbook.

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

You'd want something like this:

Please Login or Register  to view this content.

There are two problems, one trivial, one not so. The trivial problem is that End(xlUp) ignores hidden rows so it could be a lot less that the rows of data. This can be remedied by looking at the UsedRange.Rows.Count. That can be wrong but it will always be greater than or equal to the current number of rows of data.

The less trivial problem is that once you have set a value to 1 and the row is hidden, you can't see the row to unhide it, so you'll have to manually unhide all the rows to see and change the values in column Q.

Personally, I'd be inclined to use Autofilter to hide and unhide the rows.

You're welcome. Thanks for the rep.

@cajunlibra: you would use a Worksheet_Change event handler monitoring the drop down cell. Then Filter the list using Autofilter.

Note though ... Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.