HELP! Using VBA to hide/unhide rows based on a cell response

[Excel Newcomer]

Hi,

I am trying to hide/unhide rows based on a cell answer. In my case, Rows 11-15 begin unhidden. with 16 onward hidden. H11-H14 are free form. When H15 is "yes" I want rows 16-20 to show, where H20 is the next "yes" or "no" cell, 16-19 being free form, and so on. (If H15 is "No", nothing happens). My current code is as follows:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then
    For Each cel In Target
        Call Worksheet_Change(cel)
    Next cel
End If
If Target.Column = 8 Then
    If LCase(Target.Value) = LCase(Target.Offset(, 3)) Then
        Cells(Target.Offset(, 4), 1).EntireRow.Hidden = False
    Else
        Cells(Target.Offset(, 4), 1).EntireRow.Hidden = True
    End If
End If
End Sub

and the excel page snapshot is attached. How do I go about including the 4 cells in between 16 and 20?

Thank you

 

[Luke M]

How's this?

Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range
Dim checkInput As String
If Intersect(Target, Range("H:H")) Is Nothing Then Exit Sub

Application.ScreenUpdating = False
For Each c In Target
    If c.Column = 8 Then
        checkInput = UCase(c.Offset(, 3).Value)
        If checkInput = "YES" Then
            If UCase(c.Value) = checkInput Then
                Range(Cells(c.Row + 1, 1), Cells(c.Row + 4, 1)).EntireRow.Hidden = False
            Else
                Range(Cells(c.Row + 1, 1), Cells(Rows.Count, 1)).EntireRow.Hidden = True
            End If
        End If
    End If
Next c
Application.ScreenUpdating = True
End Sub

 

[Excel Newcomer]

How's this?

Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range
Dim checkInput As String
If Intersect(Target, Range("H:H")) Is Nothing Then Exit Sub

Application.ScreenUpdating = False
For Each c In Target
    If c.Column = 8 Then
        checkInput = UCase(c.Offset(, 3).Value)
        If checkInput = "YES" Then
            If UCase(c.Value) = checkInput Then
                Range(Cells(c.Row + 1, 1), Cells(c.Row + 4, 1)).EntireRow.Hidden = False
            Else
                Range(Cells(c.Row + 1, 1), Cells(Rows.Count, 1)).EntireRow.Hidden = True
            End If
        End If
    End If
Next c
Application.ScreenUpdating = True
End Sub

Works extremely well, thank you! Only exception being that this section of the code row 110. How do I adjust the code to end at a certain row and not hide the entire sheet?
because row 111 is blank, 112 contains text, and 113 I need to begin the process all over again, but this time the questions can include "complete" and "incomplete" and if "incomplete" an action has to be generated at the bottom, but regardless of complete or incomplete, the code must continue. It is a complicated application and I appreciate your help a ton.

 

[Luke M]

This line is what's hiding all rows

Range(Cells(c.Row + 1, 1), Cells(Rows.Count, 1)).EntireRow.Hidden = True

If you want to limit it, could change this to:

Range(Cells(c.Row + 1, 1), Cells(110, 1)).EntireRow.Hidden = True

 

[Excel Newcomer]

okay, that worked perfectly, thank you. Your help is extremely appreciated

I have been working on the next part of the code and have been struggling a bit. The goal for the next section is to go line by line and regardless of "yes" or "no" or "complete" or "incomplete", the line needs to proceed +1 on some occasions and +3 on others and so on. Sometimes certain cells need to be skipped as well. I created a very generic code (does not utilize all that excel has to offer) to get the logic down for myself and just have a "working version" but it is extremely inefficient and should someone ever want to add to it, it would be a big hassle.

Regardless, is there a way to not use the offset, just check the contents of a cell and if complete, continue to the desired line and if incomplete, unhide a separate section of the workbook with an action? I have attached my generic workbook for better understanding.
Also, when I tried to transfer the code from above to another sheet (sheet1), it did not work. by my understanding, I adjusted everything that needed to be, however I didnt know if there was something else I am missing?

I apologize for the winded explanation. I am having a difficult time finding the appropriate resources to help me understand how to apply VBA appropriately

Thank you

 

[Luke M]

Hmm. Since we're already using one helper column to check for values, I suggest we use another one to indicate how many rows to hide. Check out this version of your file. I hid some rows already, and I think it works pretty nicely.

 

[Excel Newcomer]

In trying to use that code on another sheet, adjusting the offset, I run into the problem where the same code does not work on a different sheet. It gets stuck after the 'Dim c as range' line

Thank you

 

[Luke M]

Odd, working just fine for me. How are you copying the sheet? Right-click on tab, copy? The next line after the Dim statement simply says:
For Each c in Target
which shouldn't be causing issues...

Here's your workbook, where I just copied the one worksheet. Everything works on my end.

 

[Excel Newcomer]

First of all, thank you again for all of your help. Much appreciated.

As for not working, on the "admin" tab, I duplicated the code that you had suggested before and that isnt working. I changed the offset and the end point. I dont know if I am missing something?

 

[Luke M]

Your admin tab has a different setup, so we can't just simply copy the code. Notice that on the "Phase 1" sheet, there's the cell containing trigger text, and then the number of rows you want to be affected (num of rows below current row). We did that since in the Phase sheets, you have different number of rows being changed at different points. For the admin, looks like it's always 3 rows. In the attached, I modifed the script on Admin sheet to suit.

 

[Excel Newcomer]

OH, i see. you define the range "P : P" because that is the column. I had my range still at "H:H"

Great thank you so much! that is all I needed!