Solved

Modify Hide Cell Macro to include multiple ranges

Posted on 2010-08-25
16
268 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

695 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