Modify Hide Cell Macro to include multiple ranges

I have the following macro and I want to expand it to include more conditions. For example, I want to say that if G5 = No, then hide rows 14:16 or if G5=U, then hide rows 18:19. In other words, I want to add more conditions.

How do I write this code((I know I'm a stupid, I need to buy a VBA book). Here's an example of the functional macro I have:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("G5")) Is Nothing Then
    If UCase(Target) = "Y" Then
        Rows("8:11").EntireRow.Hidden = True
    Else
        Rows("8:11").EntireRow.Hidden = False
    End If
End If
End Sub
Unreal1998Asked:
Who is Participating?
 
calacucciaConnect With a Mentor Commented:
The ad hoc solution


Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("G5")) Is Nothing Then
    If UCase(Target) = "Y" Then
        Rows("8:11").EntireRow.Hidden = True
    Else
        Rows("8:11").EntireRow.Hidden = False
    End If
       If UCase(Target) = "No" Then
        Rows("14:15").EntireRow.Hidden = True
    Else
        Rows("14:15").EntireRow.Hidden = False
    End If
   If UCase(Target) = "U" Then
        Rows("18:19").EntireRow.Hidden = True
    Else
        Rows("18:19").EntireRow.Hidden = False
    End If

End If
End Sub
0
 
TracyConnect With a Mentor VBA DeveloperCommented:
Modify like this:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("G5")) Is Nothing Then
        If UCase(Target) = "Y" Then
            Rows("8:11").EntireRow.Hidden = True
        ElseIf UCase(Target) = "No" Then
            Rows("14:16").EntireRow.Hidden = True
        ElseIf UCase(Target) = "U" Then
            Rows("18:19").EntireRow.Hidden = True
        Else
            Rows("8:11").EntireRow.Hidden = False
        End If
    Else
    End If
End Sub

Open in new window

0
 
TracyConnect With a Mentor VBA DeveloperCommented:
Alternatively, you can use the Select Case statement instead of multiple ElseIfs.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("G5")) Is Nothing Then
        Select Case UCase(Target)
            Case "Y"
                Rows("8:11").EntireRow.Hidden = True
            Case "No"
                Rows("14:16").EntireRow.Hidden = True
            Case "U"
                Rows("18:19").EntireRow.Hidden = True
            Case Else
                Rows("8:11").EntireRow.Hidden = False
        End Select
    Else
    End If
End Sub

Open in new window

0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

 
Unreal1998Author Commented:
Too bad I can not give 500 to both of you. Do you guys recommend a book to learn VBA?
0
 
calacucciaCommented:
Personally, I would never read a book... and never did about a computer language.

My personal approach: record macros when I don't know how the code looks, catch the right parts of recorded macros, excercise, excercise, excercise, and trying to help out others right here :-)
0
 
TracyVBA DeveloperCommented:
I have both of these books and they're pretty good references:

http://www.amazon.com/Mastering-VBA-Guy-Hart-Davis/dp/0782144365/ref=sr_1_9?ie=UTF8&s=books&qid=1282768673&sr=8-9

http://www.amazon.com/VBA-Developers-Handbook-2nd-Getz/dp/0782129781/ref=sr_1_15?ie=UTF8&s=books&qid=1282768673&sr=8-15

These books are references books though, not to be read through like a normal book.  I guess you could read it through, but your brain would freak out.

You should try parts of things, and like calacuccia said, practice, practice, practice.  That's how you'll learn VBA.
0
 
Unreal1998Author Commented:
calacucci or broomee9:

Can I add a second condition to hide other rows because on the value of a different cell?

In the macro below, I want to add a condition that says if, B81=Yes, then hide rows 88:92.



Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("G5")) Is Nothing Then
    If UCase(Target) = "Y" Then
        Rows("8:11").EntireRow.Hidden = True
    Else
        Rows("8:11").EntireRow.Hidden = False
    End If
       If UCase(Target) = "No" Then
        Rows("14:15").EntireRow.Hidden = True
    Else
        Rows("14:15").EntireRow.Hidden = False
    End If
   If UCase(Target) = "U" Then
        Rows("18:19").EntireRow.Hidden = True
    Else
        Rows("18:19").EntireRow.Hidden = False
    End If

End If
End Sub
0
 
TracyVBA DeveloperCommented:
Change to this:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("G5")) Is Nothing Then
    If UCase(Target) = "Y" Then
        Rows("8:11").EntireRow.Hidden = True
    Else
        Rows("8:11").EntireRow.Hidden = False
    End If
       If UCase(Target) = "No" Then
        Rows("14:15").EntireRow.Hidden = True
    Else
        Rows("14:15").EntireRow.Hidden = False
    End If
   If UCase(Target) = "U" Then
        Rows("18:19").EntireRow.Hidden = True
    Else
        Rows("18:19").EntireRow.Hidden = False
    End If

   If UCase(Target) = "Yes" Then
        Rows("88:92").EntireRow.Hidden = True
    Else
        Rows("88:92").EntireRow.Hidden = False
    End If

End If
End Sub
0
 
Unreal1998Author Commented:
Broomee: The target case here is for cell G5(as we have now) and also for cell B81 for which I want to use the next condition.
0
 
TracyVBA DeveloperCommented:
ok, try this:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("G5")) Is Nothing Then
    If UCase(Target) = "Y" Then
        Rows("8:11").EntireRow.Hidden = True
    Else
        Rows("8:11").EntireRow.Hidden = False
    End If
       If UCase(Target) = "No" Then
        Rows("14:15").EntireRow.Hidden = True
    Else
        Rows("14:15").EntireRow.Hidden = False
    End If
   If UCase(Target) = "U" Then
        Rows("18:19").EntireRow.Hidden = True
    Else
        Rows("18:19").EntireRow.Hidden = False
    End If
End if

If Not Intersect(Target, Range("B81")) Is Nothing Then
   If UCase(Target) = "Yes" Then
        Rows("88:92").EntireRow.Hidden = True
    Else
        Rows("88:92").EntireRow.Hidden = False
    End If

End If
End Sub
0
 
calacucciaCommented:
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("G5")) Is Nothing Then
    If UCase(Target) = "Y" Then
        Rows("8:11").EntireRow.Hidden = True
    Else
        Rows("8:11").EntireRow.Hidden = False
    End If
       If UCase(Target) = "No" Then
        Rows("14:15").EntireRow.Hidden = True
    Else
        Rows("14:15").EntireRow.Hidden = False
    End If
   If UCase(Target) = "U" Then
        Rows("18:19").EntireRow.Hidden = True
    Else
        Rows("18:19").EntireRow.Hidden = False
    End If
End If
'New condition
If Not Intersect(Target, Range("G5")) Is Nothing Then
    If UCase(Target) = "Y" Then
        Rows("88:91").EntireRow.Hidden = True
    Else
         Rows("88:91").EntireRow.Hidden = False
    End If
End If
End Sub
0
 
calacucciaCommented:
Try Broome's one, I made a range mistake :-) (G5 should be B81 at the end)
0
 
Unreal1998Author Commented:
Working for all conditions, except the new one with B81 and rows 88:92.

I really appreciate your help.
0
 
TracyVBA DeveloperCommented:
that's weird, it should work.  The only thing I can think of is to wrap it with the enable events:

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False

If Not Intersect(Target, Range("G5")) Is Nothing Then
    If UCase(Target) = "Y" Then
        Rows("8:11").EntireRow.Hidden = True
    Else
        Rows("8:11").EntireRow.Hidden = False
    End If
       If UCase(Target) = "No" Then
        Rows("14:15").EntireRow.Hidden = True
    Else
        Rows("14:15").EntireRow.Hidden = False
    End If
   If UCase(Target) = "U" Then
        Rows("18:19").EntireRow.Hidden = True
    Else
        Rows("18:19").EntireRow.Hidden = False
    End If
End if

If Not Intersect(Target, Range("B81")) Is Nothing Then
   If UCase(Target) = "Yes" Then
        Rows("88:92").EntireRow.Hidden = True
    Else
        Rows("88:92").EntireRow.Hidden = False
    End If

End If

Application.EnableEvents = True

End Sub
0
 
calacucciaCommented:
Are you sure your events have not been disabled due to a premature stop of code?
To reactivate time, save, close Excel, re-open Excel.

Or just add a bogus module and run it once.

Sub ReActivateEvents()
Application.EnableEvents = True
End Sub
0
 
Unreal1998Author Commented:
Thanks guys I got it to work! this one worked:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("G5")) Is Nothing Then
    If UCase(Target) = "Y" Then
        Rows("8:11").EntireRow.Hidden = True
    Else
        Rows("8:11").EntireRow.Hidden = False
    End If
       If UCase(Target) = "No" Then
        Rows("14:15").EntireRow.Hidden = True
    Else
        Rows("14:15").EntireRow.Hidden = False
    End If
   If UCase(Target) = "U" Then
        Rows("18:19").EntireRow.Hidden = True
    Else
        Rows("18:19").EntireRow.Hidden = False
    End If
End If
'New condition
If Not Intersect(Target, Range("G5")) Is Nothing Then
    If UCase(Target) = "Y" Then
        Rows("88:91").EntireRow.Hidden = True
    Else
         Rows("88:91").EntireRow.Hidden = False
    End If
End If
End Sub


I modified it to add multiple conditions. Here's how it looks:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("C23")) Is Nothing Then
    If UCase(Target) = "Y" Then
        Rows("27:44").EntireRow.Hidden = True
    Else
        Rows("27:44").EntireRow.Hidden = False
    End If
     
End If
'New condition
If Not Intersect(Target, Range("C14")) Is Nothing Then
    If UCase(Target) = "U" Then
        Rows("15:17").EntireRow.Hidden = False
    Else
         Rows("15:17").EntireRow.Hidden = True
    End If

    End If


'New condition
If Not Intersect(Target, Range("C23")) Is Nothing Then
    If UCase(Target) = "U" Then
        Rows(24).EntireRow.Hidden = False
    Else
         Rows(24).EntireRow.Hidden = True
    End If

    End If
End Sub
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.