Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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
3  
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
1  
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
add comment

3 Answers

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)

    ActiveSheet.AutoFilter.ApplyFilter

End Sub
share|improve this answer
add comment

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")
         .AutoFilter.ApplyFilter
    End With
End Sub

I found the information here: http://www.jkp-ads.com/articles/Excel2007TablesVBA.asp

share|improve this answer
add comment
up vote 0 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
            .CustomViews("Mine").Show
            .CustomViews("Mine").Delete
        End With


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

End Sub
share|improve this answer
add comment

Your Answer

 
discard

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.