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.

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?

share|improve this question
1  
It would help if you posted the macro code you are trying –  chris neilsen Aug 6 '12 at 1:08

1 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

share|improve this answer

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.