Need Help with VBA Code to Hide/Unhide Rows

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
2,561
Office Version
365
Platform
Windows
Try this:

Code:
[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]
 

Some videos you may like

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

808excel

New Member
Joined
Oct 4, 2019
Messages
19
You are awesome! Thanks again for all the help!

Try this:

Code:
[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

New Member
Joined
Oct 4, 2019
Messages
19
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

Well-known Member
Joined
Feb 1, 2016
Messages
2,561
Office Version
365
Platform
Windows
You're welcome.:)
 

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
2,561
Office Version
365
Platform
Windows
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

New Member
Joined
Oct 4, 2019
Messages
19
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

Well-known Member
Joined
Feb 1, 2016
Messages
2,561
Office Version
365
Platform
Windows
Let's say we use this code:
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:

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:
Code:
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".
 

Watch MrExcel Video

Forum statistics

Threads
1,089,942
Messages
5,411,411
Members
403,368
Latest member
Cyril18

This Week's Hot Topics

Top