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

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.

Answer

Right click on your sheet name, choose "View Code" and paste the code below. After pasting, click the Excel icon below "File" at the top left, or type Alt-F11, to return to the spreadsheet view.

This will enable auto-refresh. Do not forget to save the file in a format with macro support lie .xlsm.

Private Sub Worksheet_Change(ByVal Target As Range)

    If Me.FilterMode = True Then
        With Application
           .EnableEvents = False
           .ScreenUpdating = False
        End With

        With ActiveWorkbook
            .CustomViews.Add ViewName:="Mine", RowColSettings:=True
          Me.AutoFilterMode = False
            .CustomViews("Mine").Show
            .CustomViews("Mine").Delete
        End With


         With Application
           .EnableEvents = True
           .ScreenUpdating = True
        End With
    End If

End Sub