Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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.

share|improve this question
I was able to get it to work when I put that code in the Worksheet_Change() event instead of the Worksheet_Calculate() event. – F106dart Feb 24 '11 at 13:47
put this as an answer and accept it, so that others know what you did and that the question has been resolved. – jzd Feb 24 '11 at 17:26
I had to make another modification because Calculate was expecting a parameter. Now it works!. – sorin Feb 27 '11 at 15:21

I found that when I worked with tables, this didn't work. The filter was not on the sheet but on the table. this code did the trick

Private Sub Worksheet_Change(ByVal Target As Range)
    With ActiveWorkbook.Worksheets("Sheet1").ListObjects("Table1")
    End With
End Sub

I found the information here:

share|improve this answer

Exchanging the code with this seems to do the trick as well (at least in Excel 2010):

Private Sub Worksheet_Change(ByVal Target As Range)


End Sub
share|improve this answer
up vote 1 down vote accepted

Right click on your sheet name, choose view code and paste the code below. 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
        End With

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

End Sub
share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.