Hide rows based on "Yes" "NO" value cell - if formula ?



Ive got this very long formular that people need to fill in.

if you answer NO then you dont need to answer the 5 next questions.

So what I want is to say : if the answer in C71 is no then hide entire row from 72 to 77.

Was about to follow your tutoral : 

Run a Macro when a User Changes a Specific Cell, Range, or Any Cell in Excel

But its the formula that is to complex for my level ...I've looked at several tutoral but its my first macro and I get confuse.

Hope you can help



I have used the below but cannot add more cell to look at.
I am trying to replicate the below to other cell and row  :(

Sub hiderowbasedoncellvalue() Dim p As Range   For Each p In Range("c71").Cells If p.Value = "no" Then Range("72:77").EntireRow.hidden = True   End If   Next p   End Sub  
chavdv Nov 26, '19 at 8:03 am
Send me sample file 
beepetark Nov 27, '19 at 10:59 am
Add to Discussion



The code below may be too advanced for you to easily understand. I offer it because I feel you really want to learn, and because you are asking for much less than you really need. It will do what you want plus some. 

First, this is an event procedure. It will run whenever a change is made in the worksheet to which it belongs. To indicate which sheet that is it must be installed in the code module belonging to that sheet. That code module is added by Excel to each sheet as you create the tab. In the VB Editor's Project Explorer window it will be listed by a name like Sheet1 (Sheet1). It will not work if installed anywhere else.

Private Sub Worksheet_Change(ByVal Target As Range)

    Const TriggerCellAddress As String = "C7 C71"

    Dim Trigger() As String
    Dim Rng As Range
    Dim i As Integer

    Trigger = Split(TriggerCellAddress)
    For i = 0 To UBound(Trigger)
        Set Rng = Range(Trigger(i))
        With Target
            If .Address = Rng.Address Then
                Rng.Offset(1).Resize(5).EntireRow.Hidden = (StrComp("no", .Value, vbTextCompare) = 0)
                Exit For
            End If
        End With
    Next i
End Sub

When a change is made in the sheet the code will check where that change occurred. My example lists cells C7 and C71. You can change the cells which will respond in this line of code.

Const TriggerCellAddress As String = "C7 C71"

You may specify a single address or a hundred. Just make sure you leave exactly one blank space between each address, and no extra blanks, leading, trailing or in between.

If the changed cell (Target by default) has the address of one of the listed trigger cells the next 5 rows' visibility will be changed. This is the code that does that.

Rng.Offset(1).Resize(5).EntireRow.Hidden = (StrComp("no", .Value, vbTextCompare) = 0)

This code will hide or unhide (allowing the user to change his mind) the five rows starting immediately below the row of the trigger address. Note that the text comparison isn't case sensitive. "No", "no", "NO" or even "nO" will all cause the rows to be hidden. Any other entry will make them visible.


Answer the Question

You must create an account to use the forum. Create an Account or Login