VBA - Hide rows based on Cell value.

[OversizedCranium]

Hi all,

Appreciate this is pretty basic, but I'm struggling to use google to make it work,

For context I'm making a tab which is in a form style format, people fill out and all the calculations are done in the background, however I want to make it as less noisy as possible...

So when Cell C18 in this instance = "No", I want to hide rows 19-39, but when it is populated back to yes, I want the rows to come back, can anyone help me out? I Appreciate there are very similar questions to this one, but due to my absolute zero knowledge of VBA I'm struggling to translate to my own circumstance.

Thanks All,

Callum

 

[My Aswer Is This]

Try this:
This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window

Private Sub Worksheet_Change(ByVal Target As Range)
'Modified  11/12/2020  5:50:05 AM  EST
If Target.Cells.CountLarge > 1 Or IsEmpty(Target) Then Exit Sub
If Target.Address = Range("C18").Address Then
    If Target.Value = "No" Then Rows(19).Resize(21).Hidden = True
    If Target.Value = "Yes" Then Rows(19).Resize(21).Hidden = False
End If
End Sub

 

[mohadin]

Maybe

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("C18")) Is Nothing Then
        If Range("C18").Value = "NO" Then
            Rows("19:39").EntireRow.Hidden = True
        Else
            Rows("19:39").EntireRow.Hidden = False
        End If
    End If
End Sub

 

[OversizedCranium]

Try this:
This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window

Private Sub Worksheet_Change(ByVal Target As Range)
'Modified  11/12/2020  5:50:05 AM  EST
If Target.Cells.CountLarge > 1 Or IsEmpty(Target) Then Exit Sub
If Target.Address = Range("C18").Address Then
    If Target.Value = "No" Then Rows(19).Resize(21).Hidden = True
    If Target.Value = "Yes" Then Rows(19).Resize(21).Hidden = False
End If
End Sub

Life saver!

Thanks a lot

 

[OversizedCranium]

Try this:
This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window

Private Sub Worksheet_Change(ByVal Target As Range)
'Modified  11/12/2020  5:50:05 AM  EST
If Target.Cells.CountLarge > 1 Or IsEmpty(Target) Then Exit Sub
If Target.Address = Range("C18").Address Then
    If Target.Value = "No" Then Rows(19).Resize(21).Hidden = True
    If Target.Value = "Yes" Then Rows(19).Resize(21).Hidden = False
End If
End Sub

Sorry to bother you further,

What's the logic in the 21? so if I wanted to apply this to different rows and adifferent cell, what am I ammending?

Cheers,

 

[My Aswer Is This]

Sorry to bother you further,

What's the logic in the 21? so if I wanted to apply this to different rows and adifferent cell, what am I ammending?

Cheers,

Give me a couple examples of other other rows. And I will show you.
And will it always be Yes No

 

[OversizedCranium]

Give me a couple examples of other other rows. And I will show you.
And will it always be Yes No

Brilliant,

Yes so it would be referencing cell C46, hiding rows 47-49 if the cell is "no", bringing them back if cell is "yes"

Thanks for this!

 

[OversizedCranium]

Give me a couple examples of other other rows. And I will show you.
And will it always be Yes No

Is it possible as well for a formula to hide rows, if it doesn't equal " x " and anything else in the cell it's referencing, it will simply hide it?

 

[My Aswer Is This]

Is it possible as well for a formula to hide rows, if it doesn't equal " x " and anything else in the cell it's referencing, it will simply hide it?

No. Just using a formula without using vba will not hide a row

 

[My Aswer Is This]

Try this:
Add more if you want:

Private Sub Worksheet_Change(ByVal Target As Range)
'Modified  11/12/2020  7:00 AM  EST
If Target.Cells.CountLarge > 1 Or IsEmpty(Target) Then Exit Sub

Select Case Target.Address

        Case "$C$18"
            If Target.Value = "No" Then Rows("19:39").Hidden = True
            If Target.Value = "Yes" Then Rows("19:39").Hidden = False
    
        Case "$C$46"
            If Target.Value = "No" Then Rows("47:49").Hidden = True
            If Target.Value = "Yes" Then Rows("47:49").Hidden = False

End Select

End Sub