Solved

Modify Hide Cell Macro to include multiple ranges

Posted on 2010-08-25
16
254 Views
Last Modified: 2012-06-22
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
0
Comment
Question by:Unreal1998
  • 6
  • 5
  • 5
16 Comments
 
LVL 17

Accepted Solution

by:
calacuccia earned 167 total points
ID: 33525601
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
 
LVL 24

Assisted Solution

by:broomee9
broomee9 earned 333 total points
ID: 33525623
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
 
LVL 24

Assisted Solution

by:broomee9
broomee9 earned 333 total points
ID: 33525647
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Closing Comment

by:Unreal1998
ID: 33525782
Too bad I can not give 500 to both of you. Do you guys recommend a book to learn VBA?
0
 
LVL 17

Expert Comment

by:calacuccia
ID: 33525880
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
 
LVL 24

Expert Comment

by:broomee9
ID: 33525922
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
 

Author Comment

by:Unreal1998
ID: 33570849
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
 
LVL 24

Expert Comment

by:broomee9
ID: 33570890
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
 

Author Comment

by:Unreal1998
ID: 33571041
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
 
LVL 24

Expert Comment

by:broomee9
ID: 33571076
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
 
LVL 17

Expert Comment

by:calacuccia
ID: 33571094
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
 
LVL 17

Expert Comment

by:calacuccia
ID: 33571105
Try Broome's one, I made a range mistake :-) (G5 should be B81 at the end)
0
 

Author Comment

by:Unreal1998
ID: 33571167
Working for all conditions, except the new one with B81 and rows 88:92.

I really appreciate your help.
0
 
LVL 24

Expert Comment

by:broomee9
ID: 33571203
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
 
LVL 17

Expert Comment

by:calacuccia
ID: 33571221
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
 

Author Comment

by:Unreal1998
ID: 33799020
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

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

685 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question