hiding rows in excel based on words in a cell

Question

I am VERY new at VBA and have been searching for VBA code that can do what I want. I have tried several and attempted to adapt them however I just can't seem to get them right so thought I would try for some help!

I have 6 projects that have either YES or NO in the cell next to them indicating if the person is working on that project. This is determined by the persons name next to that project on another sheet, so is formula produced and not a drop down or typed in value.

The project then has a few rows below corresponding to it.

If there is a NO next to a project (in C6), I want the corresponding rows for that project to be hidden (rows 13:29).

I want this to be repeated for each project,

so a no in c7 hides 31:47, a no in C8 hides 49:65, a no in C9 hides 67:83, a no in C10 hides 85:101, a no in C11 hides 103:118,

I don't know if this is possible and have been going around in circles, really hope that someone can help :)

this is one of the adaptions i have tried but i am sure i am doing something wrong, sorry for not posting this here before

Private Sub Worksheet_Change(ByVal Target As Range) 

If Target.Address = "$C$6" Then 
If Target.Value = NO Then 
Rows(13:29).EntireRow.Hidden = True 
Else 
Rows(13:29).EntireRow.Hidden = False 

If Target.Address = "$C$7" Then 
If Target.Value = NO Then 
Rows(31:47).EntireRow.Hidden = True 
Else 
Rows(31:47).EntireRow.Hidden = False 
End If 
End If 


If Target.Address = "$C$8" Then 
If Target.Value = NO Then 
Rows(49:65).EntireRow.Hidden = True 
Else 
Rows(49:65).EntireRow.Hidden = False 
End If 
End If 

If Target.Address = "$C$9" Then 
If Target.Value = NO Then 
Rows(67:83).EntireRow.Hidden = True 
Else 
Rows(67:83).EntireRow.Hidden = False 
End If 
End If 


If Target.Address = "$C$10" Then 
If Target.Value = NO Then 
Rows(85:101).EntireRow.Hidden = True 
Else 
Rows(85:101).EntireRow.Hidden = False 

End If 
End If 



If Target.Address = "$C$11" Then 
If Target.Value = NO Then 
Rows(103:119).EntireRow.Hidden = True 
Else 
Rows(103:119).EntireRow.Hidden = False 

End If 
End If 


End Sub

Removed my downvote. Off the top of my head, without touching your code, have you tried turning `NO` to `"NO"`? :) — WGS, Jan 08 '14 at 03:58
Very interesting question. Posting an answer. You will have to be patient though as it might take a little longer than usual :) — Siddharth Rout, Jan 08 '14 at 04:03
@SiddharthRout: I have an answer as well, but he's so close to the right one, I badly want him to figure it out before I give mine. :D — WGS, Jan 08 '14 at 04:07
@BK201: I have an entirely different concept to suggest but I will wait till you post an answer :) — Siddharth Rout, Jan 08 '14 at 04:09
@SiddharthRout: Mine's a simple edit of his, no more. If you have something more interesting, feel free. :P — WGS, Jan 08 '14 at 04:09
@BK201, thank you, Yes, i made that change and also changed the ranges to ("67:83") which stopped an additional error message but still no luck,@siddhartRout, thank you i can keep trying and see if i can work it out :) — Mike, Jan 08 '14 at 04:14
No problem. Check PatricK's answer and let us know if it works enough for you. — WGS, Jan 08 '14 at 04:19
@user3171565: I might be wrong but what I understood from your post is that the "No" is getting populated in multiple cells by a formula. Is my understanding correct? — Siddharth Rout, Jan 08 '14 at 04:20
@SiddharthRout, yes that is correct, the NO or YES is formula produced, — Mike, Jan 08 '14 at 04:33
@user3171565: One last question... `C11 hides 103:118` Shouldn't this be `C11 hides 103:119`? — Siddharth Rout, Jan 08 '14 at 04:39
@SiddharthRout, yes sorry, that is also right! it should be 103:119, just looking at PatricK's code now, thank you so much for all your help! — Mike, Jan 08 '14 at 04:51

PatricK · Answer 1 · 2014-01-08T05:07:16.830

2

Shortest code I can think of:

Private Sub Worksheet_Change(ByVal Target As Range)
    On Error Resume Next
    Dim bHide As Boolean
    bHide = (InStr(1, Target.Value, "NO", vbTextCompare) > 0)
    Select Case Target.Address
        Case "$C$6"
            Rows("13:29").EntireRow.Hidden = bHide
        Case "$C$7"
            Rows("31:47").EntireRow.Hidden = bHide
        Case "$C$8"
            Rows("49:65").EntireRow.Hidden = bHide
        Case "$C$9"
            Rows("67:83").EntireRow.Hidden = bHide
        Case "$C$10"
            Rows("85:101").EntireRow.Hidden = bHide
        Case "$C$11"
            Rows("103:119").EntireRow.Hidden = bHide
    End Select
End Sub

Please test.

edited Jan 08 '14 at 05:07

answered Jan 08 '14 at 04:13

PatricK

6,375
1
21
25

+1: The exact way I've done it. No need to use `InStr`, your simple boolean check before the edit works fine. Though this has one inherent weakness that I'm sure is to surface, if this request is what I think it is. – WGS Jan 08 '14 at 04:17
1

@BK201: Well, Not the way I was doing it though as the results are being produced by a formula so `Worksheet_Change` won't work :) – Siddharth Rout Jan 08 '14 at 04:18
@SiddharthRout: Exactly why I cannot post mine. He said `NO` was coming from somewhere as well. The other one inherent weakness is... It will throw `Error 13` if you change multiple cells at the same time. Adding `On Error Resume Next` before the `bHide` line might seem like the solution, but it won't hide anything. :) – WGS Jan 08 '14 at 04:21
@BK201: No it won't throw the error if you handle it in a loop :) And that is what I was planning to suggest in my post. My post would have been based on [THIS](http://stackoverflow.com/questions/11406628/vba-code-doesnt-run-when-cell-is-changed-by-a-formula/11409569#11409569) and [THIS](http://stackoverflow.com/questions/11754047/worksheet-calculate-on-multiple-ranges) Anyways, I am waiting for OP to reply to my question which I asked right below his question. – Siddharth Rout Jan 08 '14 at 04:26
Yeah, sorry, guv. :) But covering our bases as elegantly is possible is the little heaven we make for ourselves in our niche here in SO so pardon our really brutal refining of code and hijacking of answer. ;) @SiddharthRout: Never really considered a loop as "same time" but, yeah, step-by-step change and a calculation based event are indeed best. :) – WGS Jan 08 '14 at 04:29

score 1 · Answer 2 · answered Jan 08 '14 at 04:51

so a no in c7 hides 31:47, a no in C8 hides 49:65, a no in C9 hides 67:83, a no in C10 hides 85:101, a no in C11 hides 103:118,

C11 hides 103:118? Shouldn't this be C11 hides 103:119? Let me explain.

To make your work simple, you need to find a trend on how your code would progress. See this analysis.

enter image description here

You are looking for "No" in row 6 and showing/hiding from row 13:29. Similarly you are looking for "No" in row 7 and showing/hiding from row 31:47. So if you notice then there is a trend. The difference between the row is 18. See image above. And If there is a trend then there is a possibility of a loop!

And because of this simple table I could also figure out that what you said C11 hides 103:118 is incorrect and probably a typo. The 118 had to be 119 to maintain that difference of 18

So your code can actually be condensed to (Untested)

Dim i As Long, StartRow As Long, EndRow As Long

StartRow = 13
EndRow = 29

For i = 6 To 11
    If UCase(Range("C" & i).Value) = "NO" Then
        Rows(StartRow & ":" & EndRow).EntireRow.Hidden = True
    Else
        Rows(StartRow & ":" & EndRow).EntireRow.Hidden = False
    End If
    StartRow = StartRow + 18
    EndRow = EndRow + 18
Next i

The next thing is that since the NO's are getting changed by formula, you will have to use Worksheet_Calculate and not Worksheet_Change until and unless the formula is picking up values from the same sheet and the cells which are changing C6:C11 are manually changed by a user.

Good pick on this pattern! But it will be hard for the VBA newbie to maintain if this pattern is broken (if some `NO` requires more rows to hide then other). And absolutely right about Worksheet_Calculate. — PatricK, Jan 08 '14 at 05:40
@SiddharthRout, it is working! Thank you so much for all your help! there is no way I would have come up with that code by myself trying to work it out, hope i don't break it!!, Thank you so much everyone who took the time to help me :) — Mike, Jan 08 '14 at 06:06
@user3171565: And I hope you break it!!! Best way to learn ;) Glad it worked for you :) — Siddharth Rout, Jan 08 '14 at 06:08
This can even reduce the code to `Rows(StartRow & ":" & EndRow).EntireRow.Hidden = (UCase(Range("C" & i).Value) = "NO")` to remove the IF block. — PatricK, Jan 09 '14 at 00:50

hiding rows in excel based on words in a cell

2 Answers2

Linked