How to hide/show cells in a range based on multiple values in a column.

Hello,

This is probably very easy but I'm wrecking my mind on it.

I have a range of rows and want to hide/show some of them based on the value contained in one of the columns. I'm currently using the following code:

Please Login or Register  to view this content.

This is fine to keep only the value "C" on the column C but the problem is if I want to keep more than one value unhiden. I tried separating the values by a comma "C,R" and sticking anothe line below the IF (If cl <> "R" Then cl.EntireRow.Hidden = True) and, of course, it will hide everything.

I already played a "bit" changing between True and False but to no avail. I wanted to keep this code, or something faster than it, because I also tried the

Please Login or Register  to view this content.

but it's very slow as it evaluates cell by cell.

Alternatively, is there a way to build a form with checkboxes so I could check the boxes I want to hide/unhide?

Much appreciated!

Alternatively

How about using autofilter?
Here's an example:
The code will filter out any rows that is not "D,P,Y"

Please Login or Register  to view this content.

Excel 2016 (Windows) 32 bit

	A
1	DATA
2	Y
3	F
4	D
5	P
6	Y
7	F
8	Y
9	F
10	D
11	O
12	T
13	D
14	I
15	Q

Sheet: Sheet1

RESULT:

Excel 2016 (Windows) 32 bit

	A
1	DATA
2	Y
4	D
5	P
6	Y
8	Y
10	D
13	D

Sheet: Sheet1

Hello Akuini,

Thank you very much for your quick response.

It worked nicely and it's even quicker than the code I had. One final question: What would be the reverse code. Or btter saying, to show everything eliminating all filters?

Thanks!

Hello Akuini,

I also noticed one thing that I would like to get rid, if possible. When I use the autofilter code, the filter symbol gets there hovering the first cell of the column covering the content of the cell. Is there a way to hide the filter symbol?

Thanks!

Originally Posted by Hellion

to show everything eliminating all filters?

With code:

Please Login or Register  to view this content.

OR
1. Select cell A1 or cell with Filter arrow
2. In the menu bar: click Data > Filter > Clear

Is there a way to hide the filter symbol?

Unfortunately, no.
But if you really need that, you can replace the code in post #2 with this code:
The code will first use autofilter then remove the autofilter then hide the intended rows.

Please Login or Register  to view this content.

to unhide all rows, use this:

Please Login or Register  to view this content.

Hello nankw83,

That could work but I rather not add data in indirect cells whenever possible. However, there is still a bit to resolve with Akuini's code and if we couldn't make it work, yours can come in very handy!

Thank you very much!

Hello Akuini,

That's a brilliant code and it works very quickly. Just one thing that I couldn't figure out: I have a Subtotal formula adding the unhidden columns at the top and your code makes the formula to add only the first row of the range. I can click "Calculate Full" to resolve it but it takes some time. It didn't happen with the AutoFilter. Is there a workaround for this very nice code?

Thanks

SUBTOTAL excludes rows hidden by the filter whereas my code hide the rows by this line:

Please Login or Register  to view this content.

so it won't work with your formula.
So, I think the first code is more suitable for you.

Another option:
Use Aggregate function, it works on hidden rows whether they are result of auto filter or not.

Hello nankw83,

I ended up using your solution. It worked wonderfully fast both for hiding and unhiding and didn't interfere with the functions I had.

Thank you very much!

Hello Akuini,

Thank you very much for your solutions. I learned a lot with them and already applied some in other sheets I have!

You're welcome, glad to help & thanks for the feedback.