Automatically reapply Filter (Excel 2013)

I know this has been asked in some variation or another but I have tried several macros found here and they do not seem to work. Perhaps I am doing something wrong.

I have a Table ("Table2") in a worksheet ("Dashboard") that contains monthly data arranged in rows. I have made the data fields show #NA for months I want to exclude based on dynamic criteria.
Now, I would like to automatically filter out the rows (months) with #NA so that they are not charted.
It works manually but I have to Right Click on the Table --> Filter --> Reapply every time the source data changes. How can I make this happen automatically so that the user does not have to manually reapply each time.

Thanks in advance.

Hi


you can replace your formula with

=iferror(old_formula,0)

this will convert all error values to zero..

Cheers!!

Thank you for the response but that will not work since '0' will be charted.
This is why I am using #NA for the case were I need the row filtered out.

I can get it to work manually the way I want by Reapplying the Filter each time after a change is made. The issues is that this is supposed to be a 'no touch' sheet and I would like the filter to apply automatically every time there is a change or the sheet is activated.

you can put "" instead of zero & you can always hide blanks.

Ok, thank you but I do not have an issue with what to put in a cell for a row I am trying to hide. I can do that easily but my issue is that if the data changes and you need to refresh the auto-filter, it is a manual process.
I am hoping for help with automatically re-applying the filter when there is change that way rows with data I want to ignore are hidden dynamically.

Ok, what you can do is put this code in sheet in which you want to hide rows automatically..

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Intersect(Target, Range("B1:B1000")) Is Nothing Or Target.Cells.Count > 1 Then Exit Sub
    
        If UCase(Target.Value) = "#N/A" Then
            cell.EntireRow.Hidden = True
        End If
End Sub

you can change range to specific range where you will get #N/A

Cheers!!

Thank you so much!

Originally Posted by excelliot

Ok, what you can do is put this code in sheet in which you want to hide rows automatically..

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Intersect(Target, Range("B1:B1000")) Is Nothing Or Target.Cells.Count > 1 Then Exit Sub
    
        If UCase(Target.Value) = "#N/A" Then
            cell.EntireRow.Hidden = True
        End If
End Sub

you can change range to specific range where you will get #N/A

Cheers!!

I tried to do the same but hide everything but a single value
So I changed the code like follows, but it doesn't seem to work

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Intersect(Target, Range("B3:K500")) Is Nothing Or Target.Cells.Count > 1 Then Exit Sub
    
        If UCase(Target.Value) = "#N/A" Then
            cell.EntireRow.Hidden = False
Else:    cell.EntireRow.Hidden = True
        End If
End Sub

hi

what exactly you are trying to achieve?