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



## DGB-Khafji (Dec 7, 2011)

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.


----------



## Zack Barresse (Jul 25, 2004)

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?


----------



## scotty718 (Nov 19, 2010)

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.


----------



## DGB-Khafji (Dec 7, 2011)

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.


----------



## DGB-Khafji (Dec 7, 2011)

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


----------



## Zack Barresse (Jul 25, 2004)

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 (Dec 7, 2011)

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


----------



## Keebellah (Mar 27, 2008)

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


----------



## Keebellah (Mar 27, 2008)

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 ...


----------



## DGB-Khafji (Dec 7, 2011)

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 (Mar 27, 2008)

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


```
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 (Dec 7, 2011)

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 (Mar 27, 2008)

Happy to have been able to help you.
Don't forget to press the 'Marked Solved" button to close the post


----------



## DGB-Khafji (Dec 7, 2011)

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


----------



## Keebellah (Mar 27, 2008)

Hi, No problem, I'll take a look later, just got in.


----------



## Keebellah (Mar 27, 2008)

#NA is a result of a non valid vlookup or other so you cannot say if cell is "#N/A" even though it shows that.

it's a function called ISNA, so two things, it's a sloppy way if it shows #N/A so you should always test a formula and if the result is not #N/A then .... else ....

I used the function in the VBA code but you should correct your formula to avoid these 'ugly' notifications.

I have attached the faile with the function in the VBA code to hide the rule

The syntax in a cell would be like =IF(ISNA(the formula)=TRUE(),"",< the formula>))

So first your test the formula, is ISNA() = true() that means it's not correct else the formula


----------



## DGB-Khafji (Dec 7, 2011)

Great....It really helps...., is there a chance to pay you back for your kindness? I really appreciate. 

Best regards,
DGB-Khafji


----------



## Keebellah (Mar 27, 2008)

Well, most of us, at least I did, joined TSG looking for help and stayed.
The site is held up by some very dedicated people that appreciate a donation, you could show your appreciation by making a donation to the site, it will be greatly appreciated (and your benefit is the 'removal' of the adds  and I can tell you, you can always find or get an answer here

It's up to you, and thank you for your appreciation


----------



## DGB-Khafji (Dec 7, 2011)

Thanks for the idea. I will.


----------



## DGB-Khafji (Dec 7, 2011)

Hi Hans,
It's me again. My apology for this. i am just wondering if it is possible for the VBA codes to work on the protected worksheet. 

Basically I need to protect the worksheet to avoid unneccesary deletion of the formula by the user of the template I am working with. I re-attach the same workbook with worksheet protection as 1234. i just observed that VBA doesn't work in protected worksheet.

Your kind help will be highly appreciated.

Thank you and best regards,
DGB-Khafji


----------

