Is it possible to automatically hide/unhide rows based on the contents of a cell in that row?

[slam]

I have a chart based on A1:T36

Sometimes there will a #N/A in in column A based on the results of the formula contained within.

I want those rows automatically hidden, but should the result change to anything else, I'd want it unhidden.

Is this possible? Maybe some VB that runs upon worksheet activate?

Thanks

 

[Andrew Poulsom]

You could put an AutoFilter in the Worksheet_Calculate event procedure.

 

[Jonmo1]

Sure, try this

Right click the Sheet's Tab - View Code
paste the following

Private Sub Worksheet_Calculate()
Dim MyRange As Range, C As Range 
Application.EnableEvents = False
Set MyRange = Range("A1:A36") 'Adjust range as needed.
 
For Each C In MyRange
    C.EntireRow.Hidden = IsError(C)
Next C
 
Application.EnableEvents = True
End Sub

Hope that helps.

 

[slam]

Sure, try this

Right click the Sheet's Tab - View Code
paste the following

Private Sub Worksheet_Calculate()
Dim MyRange As Range, C As Range 
Application.EnableEvents = False
Set MyRange = Range("A1:A36") 'Adjust range as needed.
 
For Each C In MyRange
    C.EntireRow.Hidden = IsError(C)
Next C
 
Application.EnableEvents = True
End Sub

Hope that helps.

I'm getting a Compile error: Object Required on My Range = and the first row is also highlighted yellow - nothing being hidden so far.

Does this run each time the workbook is opened? Can it be changed to run each time the worksheet is activated?

Incidentally, don't know if it helps, but the formula in A2 through A36 is:

=IF('Drivers & Standings'!B3="",NA(),'Drivers & Standings'!B3)

 

[Jonmo1]

I'm getting a Compile error: Object Required on My Range = and the first row is also highlighted yellow - nothing being hidden so far.

Does this run each time the workbook is opened? Can it be changed to run each time the worksheet is activated?

Incidentally, don't know if it helps, but the formula in A2 through A36 is:

=IF('Drivers & Standings'!B3="",NA(),'Drivers & Standings'!B3)

I did a typo dimming the variables as Long instead of Range.
I've already corrected it, recopy the code from the previous post.


It runs every time a formula recalculates.

 

[slam]

It runs every time a formula recalculates.

Excellent - that'll work just as well, and it works perfectly!

Thank you!

 

[Jonmo1]

Glad to help, thanks for the feedback...


But Andrew's suggestion to use autofilter will be more efficient..
With a dataset of only 36 rows, it's not that big of a deal.
But if you're going to expand it in the future, you might try this.
Assuming Row 1 is headers..

Private Sub Worksheet_Calculate()
Application.EnableEvents = False
Range("A1:T36").AutoFilter Field:=1, Criteria1:="<>#N/A"
Application.EnableEvents = True
End Sub

 

[slam]

I don't really like the look of autofilter - the arrows are messy and obscure the header row text