Efficient way to Hide all rows with a 0 in a column every time a cell value is changed

[Sky188]

Good evening all,

I'm looking for an efficient way to hide upwards of a thousand rows that have zero's in a single column every time a data validation cell is changed.

To elaborate, in A1 I'll have a data validation that brings the list of Red, Green, and Blue. If red is chosen, it shows row 2-20, hides rows 21-60. If Green is chosen, it shows rows 21-40, and hides rows 2-20, and 41-60. If Blue is chosen, it shows rows 41-60, and hides 2-40. (see below code)

Please Login or Register  to view this content.

With each section of rows hidden / shown, some of the rows have a 1 in column A, and others have a 0 (For example, when red is chosen and rows 2-20 are shown, A2 = 1, A3 = 1, A4-A10 = 0, A11-A20 = 1).

What I'm looking for is some way that will hide all these rows that have 0s, and only show the ones with 1s.

I've found multiple ways of doing this, though the majority take 30 minutes + to go through (my actual document has 9000 lines that this needs to happen with), and the one I've found (posted below) that works well, brings back an error when I try clearing the data validation list (which hides all rows (1-60).

Please Login or Register  to view this content.

[xladept]

Hi Sky,

Here's a block "hide" that might work for you:

Please Login or Register  to view this content.

[Sky188]

Worked wonderfully, Thanks!

[xladept]

You're welcome!