Auto Hide or Unhide Rows if Value is TRUE

[Bkisley]

Ok I tried doing this on my end, tried looking it up, tried other posts on this site and can't find what I need...so coming to you!

I have a data table that has slicers. When I select the Client and category in the slicer I am after, my equation in cell AW9 automatically updates. This data table has the details of all clients and all projects. After I click on my Client slicer, I narrow this down to a single client. This changes the value in Cell AW9 to TRUE. As soon as my data table is showing more than one Client's name, AW9 changes to FALSE.

This data table then is linked to a condensed summary table. In column A I have an equation that once again shows either TRUE or FALSE. If any Cell in the range A10:A1000 shows TRUE, I need the entire row to show (unhide). If any cell in Column A from A10:A1000 shows FALSE, I need that entire row to be hidden.

This macro needs to run every time AW9 changes.

Quick Recap - auto run macro when AW9 changes. if AW9 is.....
AW9 = TRUE --> unhide each row between 10:1000 where the value in column A is TRUE
AW9 = FALSE --> unhide all rows between 10:1000


THANK YOU SO MUCH IN ADVANCE

 

[Zot]

Paste this in Worksheet of your interest

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Range("AW9"), Target) Is Nothing Then
    If Target = True Then
        Range("A10", "A1000").EntireRow.Hidden = True
    Else
        Range("A10", "A1000").EntireRow.Hidden = False
    End If
End If

End Sub

 

[Bkisley]

Nothing happens when I do this...

 

[My Aswer Is This]

Nothing happens when I do this...

I tried it and it worked:
You must enter "True" in Range("AW9") the rows are hidden
If you remove True rows are visible

 

[Bkisley]

=COUNTIF(AY:AY,AY10)=COUNTIF(AY10:AY1010,"*")
Is my equation in AW9
I had that equation equal to false --> pasted the code --> changed my slicer option so AW9 went to True --> nothing happened.

I ended up finding a workaround (temporarily) aka creating a different macro that the user must click after choosing the slicer option. If you/someone can get it to automatically work, I'd still be interested, but it isn't a deal breaker for me at this point. If you don't help, I still appreciate the help!

 

[My Aswer Is This]

=COUNTIF(AY:AY,AY10)=COUNTIF(AY10:AY1010,"*")
Is my equation in AW9
I had that equation equal to false --> pasted the code --> changed my slicer option so AW9 went to True --> nothing happened.

I ended up finding a workaround (temporarily) aka creating a different macro that the user must click after choosing the slicer option. If you/someone can get it to automatically work, I'd still be interested, but it isn't a deal breaker for me at this point. If you don't help, I still appreciate the help!

On this type script we showed you the True must be entered manually.
Not the result of a formula.

 

[Bkisley]

Yeah that won't work for what I need. This has to be dynamic and linked to a formula's result.

 

[Akuini]

This has to be dynamic and linked to a formula's result.

Try using "Private Sub Worksheet_Calculate()" instead of "Private Sub Worksheet_Change"

 

[Zot]

Yeah that won't work for what I need. This has to be dynamic and linked to a formula's result.

I put the formula in AW9 and change value in other cell to change AW9. I guess that's why it worked. I think change during VBA execution will not trigger. Try Akuini suggestion.