Auto Hide or Unhide Rows if Value is TRUE

Bkisley

Board Regular
Joined
Jan 5, 2017
Messages
94
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
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
1,892
Office Version
  1. 2016
Platform
  1. Windows
Paste this in Worksheet of your interest

VBA Code:
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

Board Regular
Joined
Jan 5, 2017
Messages
94

ADVERTISEMENT

=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

Well-known Member
Joined
Jul 5, 2014
Messages
18,164
Office Version
  1. 2013
Platform
  1. Windows
=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

Board Regular
Joined
Jan 5, 2017
Messages
94

ADVERTISEMENT

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

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
3,568
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Nov 26, 2020
Messages
1,892
Office Version
  1. 2016
Platform
  1. Windows
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.
 

Forum statistics

Threads
1,148,391
Messages
5,746,435
Members
424,017
Latest member
jaka

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top