Need Help with VBA Code to Hide/Unhide Rows

[808excel]

Hi,

I have the following code, but how can I apply this to rows below A5 to act in the same way? For example, I need this to be applied to A5:A44 and then A48:A87 (possibly for more rows below so I need the code to only perform the hide/unhide within the specified areas). Also, is it possible to apply this to a cell from another sheet?

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ExitSub
If Intersect(Target, Me.Range("A5")) Is Nothing Then Exit Sub
Application.EnableEvents = False
If Target <> "" Then
Rows("6").EntireRow.Hidden = False
Else
Rows("6").EntireRow.Hidden = True
End If

ExitSub:
Application.EnableEvents = True
End Sub


Thanks!

 

[Akuini]

Try this:

[FONT=lucida console][COLOR=Royalblue]Private[/COLOR] [COLOR=Royalblue]Sub[/COLOR] Worksheet_Change([COLOR=Royalblue]ByVal[/COLOR] Target [COLOR=Royalblue]As[/COLOR] Range)
[COLOR=Royalblue]On[/COLOR] [COLOR=Royalblue]Error[/COLOR] [COLOR=Royalblue]GoTo[/COLOR] ExitSub
[COLOR=Royalblue]If[/COLOR] Target.Cells.CountLarge <> [COLOR=crimson]1[/COLOR] [COLOR=Royalblue]Then[/COLOR] [COLOR=Royalblue]Exit[/COLOR] [COLOR=Royalblue]Sub[/COLOR]
    [COLOR=Royalblue]If[/COLOR] [COLOR=Royalblue]Not[/COLOR] Intersect(Target, Range([COLOR=brown]"A5:A44, A48:A87"[/COLOR])) [COLOR=Royalblue]Is[/COLOR] [COLOR=Royalblue]Nothing[/COLOR] [COLOR=Royalblue]Then[/COLOR]
        Application.EnableEvents = [COLOR=Royalblue]False[/COLOR]
        Target.Offset([COLOR=crimson]1[/COLOR]).EntireRow.Hidden = Target = [COLOR=brown]""[/COLOR]
    [COLOR=Royalblue]ElseIf[/COLOR] [COLOR=Royalblue]Not[/COLOR] Intersect(Target, Range([COLOR=brown]"B14"[/COLOR])) [COLOR=Royalblue]Is[/COLOR] [COLOR=Royalblue]Nothing[/COLOR] [COLOR=Royalblue]Then[/COLOR]
        Application.EnableEvents = [COLOR=Royalblue]False[/COLOR]
        Worksheets([COLOR=brown]"sheet2"[/COLOR]).Rows([COLOR=brown]"138"[/COLOR]).EntireRow.Hidden = Target = [COLOR=brown]"Yes"[/COLOR]
        Worksheets([COLOR=brown]"sheet2"[/COLOR]).Rows([COLOR=brown]"139:140"[/COLOR]).EntireRow.Hidden = Target <> [COLOR=brown]"Yes"[/COLOR]
    [COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]If[/COLOR]
ExitSub:
Application.EnableEvents = [COLOR=Royalblue]True[/COLOR]
[COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]Sub[/COLOR][/FONT]

 

[808excel]

You are awesome! Thanks again for all the help!

Try this:

[FONT=lucida console][COLOR=Royalblue]Private[/COLOR] [COLOR=Royalblue]Sub[/COLOR] Worksheet_Change([COLOR=Royalblue]ByVal[/COLOR] Target [COLOR=Royalblue]As[/COLOR] Range)
[COLOR=Royalblue]On[/COLOR] [COLOR=Royalblue]Error[/COLOR] [COLOR=Royalblue]GoTo[/COLOR] ExitSub
[COLOR=Royalblue]If[/COLOR] Target.Cells.CountLarge <> [COLOR=crimson]1[/COLOR] [COLOR=Royalblue]Then[/COLOR] [COLOR=Royalblue]Exit[/COLOR] [COLOR=Royalblue]Sub[/COLOR]
    [COLOR=Royalblue]If[/COLOR] [COLOR=Royalblue]Not[/COLOR] Intersect(Target, Range([COLOR=brown]"A5:A44, A48:A87"[/COLOR])) [COLOR=Royalblue]Is[/COLOR] [COLOR=Royalblue]Nothing[/COLOR] [COLOR=Royalblue]Then[/COLOR]
        Application.EnableEvents = [COLOR=Royalblue]False[/COLOR]
        Target.Offset([COLOR=crimson]1[/COLOR]).EntireRow.Hidden = Target = [COLOR=brown]""[/COLOR]
    [COLOR=Royalblue]ElseIf[/COLOR] [COLOR=Royalblue]Not[/COLOR] Intersect(Target, Range([COLOR=brown]"B14"[/COLOR])) [COLOR=Royalblue]Is[/COLOR] [COLOR=Royalblue]Nothing[/COLOR] [COLOR=Royalblue]Then[/COLOR]
        Application.EnableEvents = [COLOR=Royalblue]False[/COLOR]
        Worksheets([COLOR=brown]"sheet2"[/COLOR]).Rows([COLOR=brown]"138"[/COLOR]).EntireRow.Hidden = Target = [COLOR=brown]"Yes"[/COLOR]
        Worksheets([COLOR=brown]"sheet2"[/COLOR]).Rows([COLOR=brown]"139:140"[/COLOR]).EntireRow.Hidden = Target <> [COLOR=brown]"Yes"[/COLOR]
    [COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]If[/COLOR]
ExitSub:
Application.EnableEvents = [COLOR=Royalblue]True[/COLOR]
[COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]Sub[/COLOR][/FONT]

 

[808excel]

If you have some time, can you also explain why the code had to be changed from IF intersect to If not intersect? I'm just trying to learn and figure out how this all works and this part is confusing because they work perfectly fine when separated and didn't know why the not was needed as it seems to be doing the opposite with that.

 

[Akuini]

You're welcome.

 

[Akuini]

Using this line:
If Intersect(Target, Range("A5:A44, A48:A87")) Is Nothing Then Exit Sub
means if the target doesn't intersect with the range then the code will exit the sub immediately.

While using this line:
If Not Intersect(Target, Range("A5:A44, A48:A87")) Is Nothing Then
means if the target doesn't intersect with the range then the code will read the "ElseIf" part
ElseIf Not Intersect(Target, Range("B14")) Is Nothing Then
and eventually will read the "End If"

 

[808excel]

Thanks for the explanation, but I'm confused as to why the original code works by itself and looks like it is saying the opposite as what you wrote.

Original

If Intersect(Target, Me.Range("B14")) Is Nothing Then Exit Sub

What you wrote.
ElseIf Not Intersect(Target, Range("B14")) Is Nothing Then

 

[Akuini]

Let's say we use this code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.CountLarge <> 1 Then Exit Sub
    If Intersect(Target, Me.Range("B14")) Is Nothing Then Exit Sub
    MsgBox "A"
End Sub

if the target doesn't intersect with B14 then the code will exit the sub immediately.
And it's ok since there are nothing to do if that happen.

But if we want to do something if the target intersect with B14 or C14, and we use this code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.CountLarge <> 1 Then Exit Sub
    If Intersect(Target, Me.Range("B14")) Is Nothing Then Exit Sub
    MsgBox "B"
    
    If Intersect(Target, Me.Range("C14")) Is Nothing Then Exit Sub
    MsgBox "C"

End Sub

then what happen if the target doesn't intersect with B14?
the code will exit the sub immediately, so it will not read "If Intersect(Target, Me.Range("C14")) Is Nothing Then Exit Sub"
which isn't what we want. We want if the target intersect with C14 then MsgBox "C".

So the proper way to do that is this:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.CountLarge <> 1 Then Exit Sub
    If Not Intersect(Target, Me.Range("B14")) Is Nothing Then
        MsgBox "B"
    ElseIf Not Intersect(Target, Me.Range("C14")) Is Nothing Then
        MsgBox "C"
    End If
End Sub

so what happen if the target doesn't intersect with B14?
the code will read "ElseIf Not Intersect(Target, Me.Range("C14")) Is Nothing Then"
and if the target intersect with C14 then MsgBox "C".

 

[808excel]

Thanks for the thorough explanation.

 

[Akuini]

You're welcome.