+ Reply to Thread
Results 1 to 13 of 13

VBA - Condense code for hiding/unhiding cell based on value in cell above

Hybrid View

  1. 07-31-2013, 12:46 PM #1
    arundh
    arundh is offline
    Registered User
    Join Date
    07-28-2013
    Location
    Chicago, Illinois
    MS-Off Ver
    Excel 2010
    Posts
    8

    VBA - Condense code for hiding/unhiding cell based on value in cell above

    Hi all,

    I have been working on this code, and I want it to check the cell above for a certain range of cells. If the cell above is empty then hide the entire row. If the cell above is not empty then show the entire row. Since this is being applied to several ranges within the same worksheet, I was wondering how to condense the coding. Any help would be great!

    This sample is to individually hide rows B29:B47 based on values in the cell above:

    Private Sub Worksheet_Change(ByVal Target As Range)
    
   ' Budget area
    If Range("B28").Value = 0 Then Range("B29").EntireRow.Hidden = True
    If Range("B29").Value = 0 Then Range("B30").EntireRow.Hidden = True
    If Range("B30").Value = 0 Then Range("B31").EntireRow.Hidden = True
    If Range("B31").Value = 0 Then Range("B32").EntireRow.Hidden = True
    If Range("B32").Value = 0 Then Range("B33").EntireRow.Hidden = True
    If Range("B33").Value = 0 Then Range("B34").EntireRow.Hidden = True
    If Range("B34").Value = 0 Then Range("B35").EntireRow.Hidden = True
    If Range("B35").Value = 0 Then Range("B36").EntireRow.Hidden = True
    If Range("B36").Value = 0 Then Range("B37").EntireRow.Hidden = True
    If Range("B37").Value = 0 Then Range("B38").EntireRow.Hidden = True
    If Range("B38").Value = 0 Then Range("B39").EntireRow.Hidden = True
    If Range("B39").Value = 0 Then Range("B40").EntireRow.Hidden = True
    If Range("B40").Value = 0 Then Range("B41").EntireRow.Hidden = True
    If Range("B41").Value = 0 Then Range("B42").EntireRow.Hidden = True
    If Range("B42").Value = 0 Then Range("B43").EntireRow.Hidden = True
    If Range("B43").Value = 0 Then Range("B44").EntireRow.Hidden = True
    If Range("B44").Value = 0 Then Range("B45").EntireRow.Hidden = True
    If Range("B45").Value = 0 Then Range("B46").EntireRow.Hidden = True
    If Range("B46").Value = 0 Then Range("B47").EntireRow.Hidden = True
        
    If Not Range("B28").Value = 0 Then Range("B29").EntireRow.Hidden = False
    If Not Range("B28").Value = 0 Then Range("B29").EntireRow.Hidden = False
    If Not Range("B29").Value = 0 Then Range("B30").EntireRow.Hidden = False
    If Not Range("B30").Value = 0 Then Range("B31").EntireRow.Hidden = False
    If Not Range("B31").Value = 0 Then Range("B32").EntireRow.Hidden = False
    If Not Range("B32").Value = 0 Then Range("B33").EntireRow.Hidden = False
    If Not Range("B33").Value = 0 Then Range("B34").EntireRow.Hidden = False
    If Not Range("B34").Value = 0 Then Range("B35").EntireRow.Hidden = False
    If Not Range("B35").Value = 0 Then Range("B36").EntireRow.Hidden = False
    If Not Range("B36").Value = 0 Then Range("B37").EntireRow.Hidden = False
    If Not Range("B37").Value = 0 Then Range("B38").EntireRow.Hidden = False
    If Not Range("B38").Value = 0 Then Range("B39").EntireRow.Hidden = False
    If Not Range("B39").Value = 0 Then Range("B40").EntireRow.Hidden = False
    If Not Range("B40").Value = 0 Then Range("B41").EntireRow.Hidden = False
    If Not Range("B41").Value = 0 Then Range("B42").EntireRow.Hidden = False
    If Not Range("B42").Value = 0 Then Range("B43").EntireRow.Hidden = False
    If Not Range("B43").Value = 0 Then Range("B44").EntireRow.Hidden = False
    If Not Range("B44").Value = 0 Then Range("B45").EntireRow.Hidden = False
    If Not Range("B45").Value = 0 Then Range("B46").EntireRow.Hidden = False
    If Not Range("B46").Value = 0 Then Range("B47").EntireRow.Hidden = False

End Sub
    Last edited by JBeaucaire; 07-31-2013 at 12:48 PM. Reason: Added CODE tags, as per Forum Rules. Take a moment to read the Forum Rules in the menu bar above. Thanks.
    Reply With QuoteRegister To Reply
  2. 07-31-2013, 01:01 PM #2
    JOHN H. DAVIS
    JOHN H. DAVIS is offline
    Forum Expert
    Join Date
    06-12-2012
    Location
    Ridgefield Park, New Jersey
    MS-Off Ver
    Excel 2003,2007,2010
    Posts
    10,241

    Re: VBA - Condense code for hiding/unhiding cell based on value in cell above

    Maybe:

    Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("B29:B47")) Is Nothing Then
    If Target.Offset(1).Value = 0 Then Target.EntireRow.Hidden = True
End If
End Sub
    Reply With QuoteRegister To Reply
  3. 07-31-2013, 01:55 PM #3
    arundh
    arundh is offline
    Registered User
    Join Date
    07-28-2013
    Location
    Chicago, Illinois
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: VBA - Condense code for hiding/unhiding cell based on value in cell above

    John, thanks for your reply. Your formula seems to hide the row of the cell where the data is entered and not the row below. Any suggestions?
    Reply With QuoteRegister To Reply
  4. 07-31-2013, 02:16 PM #4
    JOHN H. DAVIS
    JOHN H. DAVIS is offline
    Forum Expert
    Join Date
    06-12-2012
    Location
    Ridgefield Park, New Jersey
    MS-Off Ver
    Excel 2003,2007,2010
    Posts
    10,241

    Re: VBA - Condense code for hiding/unhiding cell based on value in cell above

    OK then I might be a little confused.

    Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("B29:B47")) Is Nothing Then
    If Target.Offset(1).Value = 0 Then Target.Offset(1).EntireRow.Hidden = True
End If
End Sub
    Reply With QuoteRegister To Reply
  5. 07-31-2013, 03:00 PM #5
    arundh
    arundh is offline
    Registered User
    Join Date
    07-28-2013
    Location
    Chicago, Illinois
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: VBA - Condense code for hiding/unhiding cell based on value in cell above

    That works great, I tried reversing it to unhide the same row if the cell above contains any value/text, but that didn't seem to work. Any ideas?
    Reply With QuoteRegister To Reply
  6. 07-31-2013, 03:12 PM #6
    JOHN H. DAVIS
    JOHN H. DAVIS is offline
    Forum Expert
    Join Date
    06-12-2012
    Location
    Ridgefield Park, New Jersey
    MS-Off Ver
    Excel 2003,2007,2010
    Posts
    10,241

    Re: VBA - Condense code for hiding/unhiding cell based on value in cell above

    Maybe:

    Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("B29:B47")) Is Nothing Then
    If Target.Offset(1).Value = 0 Then Target.Offset(1).EntireRow.Hidden = True
    If Target.Offset(-1).Value <> 0 Then Target.Offset(-1).EntireRow.Hidden = False
End If
End Sub
    Reply With QuoteRegister To Reply
  7. 07-31-2013, 03:59 PM #7
    arundh
    arundh is offline
    Registered User
    Join Date
    07-28-2013
    Location
    Chicago, Illinois
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: VBA - Condense code for hiding/unhiding cell based on value in cell above

    It still does not seem to work. See attached sample workbook.

    Test file.xlsm

    ScreenShot1.jpgScreenShot2.jpg
    Reply With QuoteRegister To Reply
  8. 08-01-2013, 06:28 AM #8
    JOHN H. DAVIS
    JOHN H. DAVIS is offline
    Forum Expert
    Join Date
    06-12-2012
    Location
    Ridgefield Park, New Jersey
    MS-Off Ver
    Excel 2003,2007,2010
    Posts
    10,241

    Re: VBA - Condense code for hiding/unhiding cell based on value in cell above

    Maybe:

    Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A3:A5")) Is Nothing Then
    If Target.Offset(-1).Value = 0 Or Target.Offset(-1) = "" Then Target.EntireRow.Hidden = True
End If
End Sub
    Reply With QuoteRegister To Reply
  9. 08-01-2013, 12:33 PM #9
    JBeaucaire
    JBeaucaire is offline
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: VBA - Condense code for hiding/unhiding cell based on value in cell above

    Wthout talking in code, can you explain briefly HOW you want this to operate? In realtime? Or just when you manually run a macro?

    For instance....

    "In realtime, I'd like all the rows with no value in B29:B47 to be hidden"
    "In realtime, I'd like all the rows with no value in B29:B47 to hide the row below, even if the row below has a value"
    "Only when I run a macro by hand, I'd like all the rows with no value in B29:B47 to he hidden"
    ...etc.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)
    Reply With QuoteRegister To Reply
  10. 08-02-2013, 07:20 AM #10
    arundh
    arundh is offline
    Registered User
    Join Date
    07-28-2013
    Location
    Chicago, Illinois
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: VBA - Condense code for hiding/unhiding cell based on value in cell above

    John - thank you for your quick reply. For some reason it's still not quite working.

    JBeaucaire - In real time I want row B29:B47 to be hidden. Once a value (number or text) is entered in B28 I want to show row B29. Subsequently once a value has been entered in B29 I want to show row B30, and so forth. This should all operate automatically (e.g., Private Sub Worksheet_Change(ByVal Target As Range)) without manually running a macro. The goal is to hide extra rows to make the worksheet more manageable and then unhide them once needed. Any suggestions?
    Reply With QuoteRegister To Reply
  11. 08-02-2013, 10:35 AM #11
    JBeaucaire
    JBeaucaire is offline
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: VBA - Condense code for hiding/unhiding cell based on value in cell above 

    Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("B28:B46")) Is Nothing Then
    If Target.Value = "" Then
        Target.Offset(1).EntireRow.Hidden = True
    Else
        Target.Offset(1).EntireRow.Hidden = False
    End If
End If
End Sub
    Reply With QuoteRegister To Reply
  12. 08-02-2013, 01:55 PM #12
    arundh
    arundh is offline
    Registered User
    Join Date
    07-28-2013
    Location
    Chicago, Illinois
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: VBA - Condense code for hiding/unhiding cell based on value in cell above

    This works perfectly, thanks for your assistance!
    Reply With QuoteRegister To Reply
  13. 08-02-2013, 03:19 PM #13
    JBeaucaire
    JBeaucaire is offline
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: VBA - Condense code for hiding/unhiding cell based on value in cell above

    I've marked this thread as SOLVED for you.

    Next time, select Thread Tools from the links above to mark a thread as SOLVED. Thanks.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Unhiding / Hiding rows based on a different cell value
    By gm2612 in forum Excel Programming / VBA / Macros
    Replies: 18
    Last Post: 07-30-2013, 05:15 AM
  2. [SOLVED] Help with hiding or unhiding multiple ranges of rows based on cell value on another sheet
    By Jake Bierly in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 07-22-2013, 12:14 PM
  3. Hiding and unhiding worksheets based on a cell value from another worksheet
    By J_barber in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-05-2013, 01:02 PM
  4. [SOLVED] Hiding / unhiding pictures based on cell value
    By supern0va in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-14-2012, 12:00 PM
  5. Hiding and Unhiding Entire Columns based on Cell Reference
    By montego in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-02-2010, 01:25 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1