1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Formula with auto hide and unhide rows based on result value

Discussion in 'Business Applications' started by DGB-Khafji, Dec 7, 2011.

Thread Status:
Not open for further replies.
Advertisement
  1. DGB-Khafji

    DGB-Khafji Thread Starter

    Joined:
    Dec 7, 2011
    Messages:
    10
    Good day everyone.
    Basically I need to have a formula in excel that includes auto hide and unhide of rows based on the result value. Attached is my sample exercise for quick reference. In this exercise, I want to hide automatically the rows under "REPORT OUTPUT" that contains "0" ZERO value. Basic guide: Once you enter value (from 1 to 5) in cell D3, report output will automatically calculate...... The missing condition in the formula is to automatically hide ZERO value.... Please help.
    Thanks in advance.
     
  2. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    Hi there, welcome to the board!

    Unfortunately you cannot do this with a formula. You can, however, do this with VBA. As I don't see your spreadsheet, I'm not sure I understand your data structure. What if you want to unhide the rows though? How will you determine when that will be?
     
  3. scotty718

    scotty718

    Joined:
    Nov 19, 2010
    Messages:
    185
    Welcome to the board, DGB-Khafji.

    Unfortunately, you did not attach any files. Also, you cannot have an excel formula that hides/unhides rows. You can howver, have VBA code that does that, which I am happy to build for you, if you need it.

    Alternatively, you can have some conditional formatting that will allow cells to be grayed-out, or have their text "whited-out" when conditions are met, which could potentiall solve your problem without code.

    Please submit your file, let me know me if VBA code is okay to use, and which Excel version you are running.
     
  4. DGB-Khafji

    DGB-Khafji Thread Starter

    Joined:
    Dec 7, 2011
    Messages:
    10
    Thanks Zack. It is clear to me now that this couldn't be done through excel formula. I have no idea about CBA codes. Please see attached excel sheet. Thanks.
     

    Attached Files:

  5. DGB-Khafji

    DGB-Khafji Thread Starter

    Joined:
    Dec 7, 2011
    Messages:
    10
    Thanks Scotty718. Now I know that it is not possible to do it though excel formula. Would you mind to insert the VBA to the attached excel sheet? Honestly I don't know about VBA codes....Thanks
     

    Attached Files:

  6. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    I think you missed the questions. Can you answer them? It will help in determining an actual solution. The code isn't a problem, but it'll be better for us to know exactly what you need in order to put it together.
     
  7. DGB-Khafji

    DGB-Khafji Thread Starter

    Joined:
    Dec 7, 2011
    Messages:
    10
    Kindly see attachment. Fact and requirement are written in cells A1:B2. I know how to filter and hide rows by clicking "Data, filter........" or by highlighting rows to hide..... My requirement is to know other option to automatically hide rows as per my criteria. Please see attached excel sheet for your quick reference. My pleasure then if you could help me.

    Thanks and best regards,
    DGB-Khafji
     

    Attached Files:

  8. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,559
    First Name:
    Hans
    I did something similar here
    Check the post by wschimmel "Excel row hiding based on conditions"
    I'll take a look here too if it's okay with you guys
     
  9. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,559
    First Name:
    Hans
    I've reattached your sheet and some code in the sheet's VBA.

    Is this the idea?

    Just change anything in the cells below and ...
     
  10. DGB-Khafji

    DGB-Khafji Thread Starter

    Joined:
    Dec 7, 2011
    Messages:
    10
    Hi Hans,

    Thanks a lot. It looks great. Would you mind to send the how's in doing it? I just want to try the same exercise in my actual work here.

    Thanks once again.
    DGB-Khafji
     
  11. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,559
    First Name:
    Hans
    The VBA code is in there, there's nothing magic. to it.
    I just put in the lines of code in the sheet's VBA project

    Code:
    Option Explicit
    
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Count > 1 Then Exit Sub
        If (Target.Row >= 25 And Target.Row <= 53) And (Target.Column >= 3 And Target.Column <= 7) Then
            Dim rng As Range
            Application.ScreenUpdating = False
            For Each rng In Range("C8:C18")
                Select Case rng.Value
                Case Is > 0
                    Cells(rng.Row, 1).EntireRow.Hidden = False
                Case Else
                    Cells(rng.Row, 1).EntireRow.Hidden = True
                End Select
            Next rng
            Application.ScreenUpdating = True
        End If
    End Sub
    
     
  12. DGB-Khafji

    DGB-Khafji Thread Starter

    Joined:
    Dec 7, 2011
    Messages:
    10
    Thank you so much. Great. i got it. I can now apply this to my live data. It helps a lot.
    Best regards,
    DGB-Khafji
     
  13. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,559
    First Name:
    Hans
    Happy to have been able to help you.
    Don't forget to press the 'Marked Solved" button to close the post
     
  14. DGB-Khafji

    DGB-Khafji Thread Starter

    Joined:
    Dec 7, 2011
    Messages:
    10
    Hi Hans,

    I tried to use your previous VBA codes solving my previous post about autohiding rows based on formula result. I just can't managed to apply it to my new requirement in autohiding the formula result like, "#N/A"
    Attached is my sample exercise. Would appreciate if you can help me solving my requirement to autohide formula results, "ZERO, 0" and "#N/A" combined.

    Thanks and best regards,
    DGB-Khafji
     

    Attached Files:

  15. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,559
    First Name:
    Hans
    Hi, No problem, I'll take a look later, just got in.
     
  16. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1030100

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice