?
Solved

Modify Hide Cell Macro to include multiple ranges

Posted on 2010-08-25
16
Medium Priority
?
281 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 668 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:Tracy
Tracy earned 1332 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:Tracy
Tracy earned 1332 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

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:Tracy
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:Tracy
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:Tracy
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:Tracy
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

Independent Software Vendors: 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!

Question has a verified solution.

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

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

807 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