Hide rows in a different sheet based on a value (that contains specific letters)

Copper Contributor

I have 2 sheets and attached an example:


Sheet1 D8 has Baby as value
Sheet1 D9 has Man as value
Sheet1 D10 has Woman as value

The code to search for "ab" in column D and if it finds it somewhere to hide row 8 from sheet2
Second condition is to search for ma in column D to hide row 9 from sheet2
Third condition is to search for om in column D to hide row 10 from sheet2

FYI - I will need to have 6 more conditions, but let's see if there is such code where it searches for containing letters in a column and also the code to be automatic not to run it every time and if there is nothing written in column D to

unhide all rows from sheet2.

Thank You!

11 Replies

@Steiny88 

"ma" occurs in D9 and D10. Doesn't that cause confusion?

I didn't realize that :D
Let's say to use wo from woman as I will not have any duplications in the original file.

@Steiny88 

See the attached workbook. It is now a macro-enabled workbook (*.xlsm) so you'll have to allow macros when you open it.

To view the code, right-click the sheet tab of Sheet1 and select 'View Code' from the context menu.

That works perfectly.

I have one more question. In the code I cannot see which rows to hide as in my original file I need to hide for example Row 8 and then Row 19 then Row 32 but in the code I don't understand how it knows which row to hide. If there is ab to select which exact rows to hide - for example for ab to hide row 8 and 19, how can I do that in the code?
Thanks a lot!

@Steiny88 

The code is the Worksheet_Change event procedure of Sheet1. It runs automatically when you change the value of one or more cells on Sheet1.

The code first checks whether the changed cell is in column D.

If so, it unhides the corresponding row on Sheet2, then checks the new value of the cell. If it contains "ab, "ma" or "om", it hides the corresponding row on Sheet2, otherwise it leaves it visible.

Since the code reacts to you changing a cell, it "knows" which row to hide/unhide on Sheet2: the row with the same number.

 

Yes, that's what I want to fix, if it sees ab to hide for example 2 rows which are not corresponding to the rows where this ab is located on sheet1. If in D8 I write Baby, to hide for example 2 entirely different rows like 19 and 32.

@Steiny88 

Apparently, I have completely misinterpreted your request. Now I don't understand anymore.

How should I know that you want to look for "ba" if you write "Baby"?

I didn't say ba, I want to look for ab and this is just an example. The original file it's different but I will modify it so I need only the code. The word doesn't matter, only the condition to check for the specific letters and ab is just an example. If it sees ab somewhere in column D on sheet1 to choose which rows to hide on sheet2. This is what I need. I hope that helped.

@Steiny88 

Sorry, my mistake. How should I know that you want to look for "ab" if you enter Baby?

Sorry I don't understand. It's in my initial post to search for the letters ab and I gave an example with baby but it can be any other word that contains the letters ab written together, for example Gabriel.

The code that you pasted in the sheet it hides rows based on where the actual condition is located in column D in sheet1. I want to specify exactly which row/s to hide if it sees anywhere in column D not only in cell D8.

@Steiny88 

I don't understand either. I give up for now.