Solved

Modify Hide Cell Macro to include multiple ranges

Posted on 2010-08-25
16
223 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
 

Author Closing Comment

by:Unreal1998
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

Author Comment

by:Unreal1998
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Try Broome's one, I made a range mistake :-) (G5 should be B81 at the end)
0
 

Author Comment

by:Unreal1998
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

728 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now