Auto-refreshing auto-filter in excel, update

Question

How to I auto-refresh an Excel auto-filter when data is changed?

Use case: I change the value of one cell to a value that was filtered. I want to see the current row disappearing without having to do anything else.

It seems it doesn't work with more complex cases:

suppose you have a sheet, and a table of 2 columns: col A: formula evaluation result, a number; col B = Name; The task is easy: create a filter of names adn numbers based on number criteria >0

What you do, is using autofilter for that, and it works. But not auto-refreshing itself.

The Macros proposed somehow doesn't want to work:

• First, I create auto-filter and apply the criteria (works)
• Second, I change content of adjacent sheets, which causes acive sheet (col A) to change
• Macros runs, but it doesn't re-filter the list: old records are still there, but with new numbers (even 0, when I use >0 criteria). -Then the Macros turns off the Auto-filter mode and button, so I loose my selection and have to re-select manually.

Total failure.... any comments?

Answer 1

Try this

Place this in your ThisWorkbook module

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    If TypeName(Sh) = "Worksheet" Then
        With Sh
            If .AutoFilterMode Then
                If Not Intersect(.AutoFilter.Range, Target) Is Nothing Then
                    .AutoFilter.ApplyFilter
                End If
            End If
        End With
    End If
End Sub

Then whenever you type something into a cell that is part of a filter range, the filter is refreshed