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

 

[OversizedCranium]

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

Brilliant starting to understand it now...

Final one,

Can you do a double parameter.. so if C15 = "Residential" and C18 = "Yes" it would hide 19 to 37 and show 38 to 45 (rows)

 

[My Aswer Is This]

Brilliant starting to understand it now...

Final one,

Can you do a double parameter.. so if C15 = "Residential" and C18 = "Yes" it would hide 19 to 37 and show 38 to 45 (rows)

This can be done. But previously we were wanting the script to run when a specific cell value was change. But now your wanting to change two cell value C15 and C18 so when would the script run when C15 is changed or C18 is changed.
And it's always nice to know the ultimate goal. Will all this involve maybe 20 different ranges

But you can look at this script to see a example:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.CountLarge > 1 Or IsEmpty(Target) Then Exit Sub
'Modified  11/12/2020  10:50:17 PM  EST

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

        Case "$C$15"
            If Target.Value = "Residential" And Range("C18").Value = "Yes" Then
                Rows("19:37").Hidden = True
                Rows("38:45").Hidden = False
            End If
               
        Case "$C$18"
            If Target.Value = "Yes" And Range("C15").Value = "Residential" Then
                Rows("19:37").Hidden = True
                Rows("38:45").Hidden = False
            End If
           
End Select

End Sub

 

[OversizedCranium]

This can be done. But previously we were wanting the script to run when a specific cell value was change. But now your wanting to change two cell value C15 and C18 so when would the script run when C15 is changed or C18 is changed.
And it's always nice to know the ultimate goal. Will all this involve maybe 20 different ranges

But you can look at this script to see a example:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.CountLarge > 1 Or IsEmpty(Target) Then Exit Sub
'Modified  11/12/2020  10:50:17 PM  EST

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

        Case "$C$15"
            If Target.Value = "Residential" And Range("C18").Value = "Yes" Then
                Rows("19:37").Hidden = True
                Rows("38:45").Hidden = False
            End If
              
        Case "$C$18"
            If Target.Value = "Yes" And Range("C15").Value = "Residential" Then
                Rows("19:37").Hidden = True
                Rows("38:45").Hidden = False
            End If
          
End Select

End Sub

Apologies for this, was just thinking as I went. As I wasn;t entirely sure what could or couldn't be done, I'll have a full think and get back to you!

Thanks for your help

 

[OversizedCranium]

This can be done. But previously we were wanting the script to run when a specific cell value was change. But now your wanting to change two cell value C15 and C18 so when would the script run when C15 is changed or C18 is changed.
And it's always nice to know the ultimate goal. Will all this involve maybe 20 different ranges

But you can look at this script to see a example:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.CountLarge > 1 Or IsEmpty(Target) Then Exit Sub
'Modified  11/12/2020  10:50:17 PM  EST

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

        Case "$C$15"
            If Target.Value = "Residential" And Range("C18").Value = "Yes" Then
                Rows("19:37").Hidden = True
                Rows("38:45").Hidden = False
            End If
              
        Case "$C$18"
            If Target.Value = "Yes" And Range("C15").Value = "Residential" Then
                Rows("19:37").Hidden = True
                Rows("38:45").Hidden = False
            End If
          
End Select

End Sub

Hello,

After a couple of days thinking, I've got what I think is now needed,

Bear with me as this is quite a few paramters so not sure if it's even possible and if you'd like a copy of the sheet to make it easier thats fine,

If cell C18 = "Yes" I want row 19 to appear.

Here I have a drop down of 3 options in Cell C19,
If C19 = "Residential" I was rows 20-33 showing and hiding rows 34-46
If c19 = "Education" I want rows 20-33 hidden and rows 34- 38 showing, 39-46 hiding
If C19 = "Res & Ed" I want rows 20-38 showing, rows 39-46 hiding
IF C19 = "Part Time" I want rows 39-46 showing and 20-38 hiding

I'm not sure if this is possible?

Thanks again!

 

[My Aswer Is This]

What you want is possible.
You said: If cell C18 = "Yes" I want row 19 to appear.

But you never said how row 19 will ever be hidden
In some of your earlier post you said if C18="No" the row 19 would be hidden.
But you did not mention this in your last posting

 

[OversizedCranium]

What you want is possible.
You said: If cell C18 = "Yes" I want row 19 to appear.

But you never said how row 19 will ever be hidden
In some of your earlier post you said if C18="No" the row 19 would be hidden.
But you did not mention this in your last posting

Apologies same applies! C18 ='no' and 19 is hidden

 

[OversizedCranium]

What you want is possible.
You said: If cell C18 = "Yes" I want row 19 to appear.

But you never said how row 19 will ever be hidden
In some of your earlier post you said if C18="No" the row 19 would be hidden.
But you did not mention this in your last posting

So for the logic of it, it's basically additional hours, if the are additional hours required is yes, it opens what type? so C18 = yes row 19 opens up for what type, so C18 = "No", 19 to 46 are hidden.

then the what type is for the above,

Which is all then dependent on c19,

 

[My Aswer Is This]

We are up to post number 16 now. And I have become lost.
Earlier you said you understood my code and how it worked. But now you seem to be adding more to what you want and for some reason cannot make the changes to my code yourself.
So please in your next post give me all the specifics of what you want.

If this hide these rows
If this show these rows.