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

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

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.
 
Joined
Jul 25, 2004
Messages
5,456
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?
 
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.
 
Joined
Jul 25, 2004
Messages
5,456
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.
 

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
 

Attachments

Keebellah

Trusted Advisor
Joined
Mar 27, 2008
Messages
6,611
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
 

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
 

Keebellah

Trusted Advisor
Joined
Mar 27, 2008
Messages
6,611
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
 

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
 

Keebellah

Trusted Advisor
Joined
Mar 27, 2008
Messages
6,611
First Name
Hans
Happy to have been able to help you.
Don't forget to press the 'Marked Solved" button to close the post
 

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
 

Attachments

Keebellah

Trusted Advisor
Joined
Mar 27, 2008
Messages
6,611
First Name
Hans
Hi, No problem, I'll take a look later, just got in.
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

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 807,865 other people just like you!

Latest posts

Staff online

Top