21

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.

3
  • 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, 2011 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, 2011 at 17:26
  • I had to make another modification because Calculate was expecting a parameter. Now it works!.
    – sorin
    Feb 27, 2011 at 15:21

7 Answers 7

11

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
5

Just to consolidate the answer(s):

Sorin says:

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.

And Chris used this code (which I just did in 2010):

Private Sub Worksheet_Change(ByVal Target As Range)

    ActiveSheet.AutoFilter.ApplyFilter

End Sub

If you don't expand the post, you only see the long answer! ;)

4

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

2

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
1

I use a VBA/Macro based on Worksheet_Change event too, but my approach is slightly different... Ok, first the code and then the explanations:

Private Sub Worksheet_Change(ByVal Target As Range)
    ' first remove filter
    ActiveSheet.Range("$L$1:$L$126").AutoFilter Field:=1        
    ' then apply it again
    ActiveSheet.Range("$L$1:$L$126").AutoFilter Field:=1, Criteria1:="<>0"
End Sub

(Use Alt+F11 keys combination to make development panel appear and paste the code to the worksheet containing the filter you want to get auto-refreshed.)

In my example I'm assuming to have a simple filter on a single column (L in my case) and that my data range is on rows from 1 (even if it may contain heading) to 126 (choose a number great enough to be sure). The operation is simple: as something gets changed on my sheet, the filter on the specified range gets removed/reapplied again so to have it refreshed. What need a bit of explanation here are Field and Criteria.

The Field is an integer offset of range. In my case, I only have a single column filter and the range is made by a single column (L) which is the first in the range (therefore I use 1 as value).

The Criteria is a string that describes the filter to apply to data range. In my example, I want to show only rows where L column differs from 0 (hence I used "<>0").

That's all. For more reference on Range.AutoFilter method, see: https://msdn.microsoft.com/en-us/library/office/ff193884.aspx

-1

Sorry, insufficient rep to comment. (Admins, feel free to cut this into a comment above.) User "danicotra" response beginning with "I use a VBA/Macro based on Worksheet_Change event too, but my approach..." with
' first remove filter
' then apply it again
is the correct solution when using Excel 2007+. However .AutoFilter.ApplyFilter is invalid in XL03 and earlier so I show the way below.

I beg that true experts and gurus read the code because I'm pretty confident that it is top shelf material. Perhaps the inexplicable downvote count on this answer can be reversed when people see what good stuff is done below.

danicotra used a simplified example. Actually, you can do this more generally. Assume With ActiveSheet for the following (or some other sheet object):

  1. Save the range of the autofilter. It has .AutoFilter.Filters.Count columns, and (.AutoFilter.Range.Count/.AutoFilter.Filters.Count) rows, saved to rngAutofilter

  2. Collect in an array myAutofilters each of the 4 properties of each of the .AutoFilter.Filters.Count autofilter Items, taking care that you avoid "Application-defined errors" when .On or .Operator is false. (myAutofilters would be reDim'd to the number of rows and columns in step 1)

  3. Turn off the filter but preserve the dropdowns with .ShowAllData

  4. For each filter Item that was .On according to your saved array, reset 3 of the 4 properties of each of the .AutoFilter.Filters.Count autofilter Items. Again take care that you avoid "Application-defined errors" when .Operator is false, so for each item "i",
    rngAutofilter.AutoFilter Field:=i, Criteria1:=myAutofilters(i,2)
    or
    rngAutofilter.AutoFilter Field:=i, Criteria1:=myAutofilters(i,2), Operator:=myAutofilters(i,3), Criteria2:=myAutofilters(i,4)

Now the autofilter will be reinstituted, over the same range as it was before your code began, but with the autofilter updated for changes in data.

Public myAutofilters As Variant, rngAutofilter As Range 'Public
Sub SaveAndRestoreAutofilters()
  'This will update the autofilter display to recognize data changes by turning autofilter off and then on, preserving all characteristics
  'Note, XL2007 and later have .autofilter.applyfilter, but not the invaluable XL03 and earlier
  Dim i As Long, iNumAutofilters As Long, iNumActiveAutofilters As Long
  iNumActiveAutofilters = SaveAutoFilterInfo(iNumAutofilters) 'NOTE! Use CALL or assignment to prevent parentheses from forcing ByVal !
  If iNumActiveAutofilters < 1 Then
      Application.StatusBar = "0 ACTIVE filters;" & iNumAutofilters & " autofilters"
      Exit Sub
  End If
  ActiveSheet.ShowAllData

  Rem Here optionally do stuff which can include changing data or toggling autofilter columns

  For i = 1 To iNumAutofilters
      If myAutofilters(i, 1) Then
          If myAutofilters(i, 3) <> 0 Then 'then .Operator is something, so set it and Criteria2, else just Criteria1
              rngAutofilter.AutoFilter Field:=i, Criteria1:=myAutofilters(i, 2), Operator:=myAutofilters(i, 3), Criteria2:=myAutofilters(i, 4) ', On:=true by rule
          Else
              rngAutofilter.AutoFilter Field:=i, Criteria1:=myAutofilters(i, 2) ', On:=true by rule (it's R/O anyway)
          End If
          Rem Selection.AutoFilter Field:=i 'How you'd "turn off" only a single column's autofiltering. FYI .On is R/O!
      End If
      'activesheet.autofiltermode=false 'just FYI, how you comprehensively turn off filtering on a sheet (erasing the dropdowns and criteria and filter range!)
  Next i
End Sub
Function SaveAutoFilterInfo(iNumAutofilters As Long) As Long
  Dim i As Long, iRowsAutofiltered As Long
  SaveAutoFilterInfo = 0 'counts the number that are .On, and returns the total
  iNumAutofilters = ActiveSheet.AutoFilter.Range.Columns.Count
  If ActiveSheet.AutoFilter.Filters.Count <> iNumAutofilters Then MsgBox "I can't explain this. All bets are off. Aborting.": Exit function
  ReDim myAutofilters(1 To iNumAutofilters, 4)
  For i = 1 To iNumAutofilters
      myAutofilters(i, 1) = ActiveSheet.AutoFilter.Filters(i).On
      If myAutofilters(i, 1) Then
          SaveAutoFilterInfo = SaveAutoFilterInfo + 1
          myAutofilters(i, 2) = ActiveSheet.AutoFilter.Filters(i).Criteria1
          myAutofilters(i, 3) = ActiveSheet.AutoFilter.Filters(i).Operator
          If myAutofilters(i, 3) <> 0 Then 'then is either xlAnd, xlOr, etc., and there's a second criteria
              myAutofilters(i, 4) = ActiveSheet.AutoFilter.Filters(i).Criteria2
          End If
      End If
  Next i
  iRowsAutofiltered = ActiveSheet.AutoFilter.Range.Count / ActiveSheet.AutoFilter.Range.Columns.Count
  Set rngAutofilter = Cells(ActiveSheet.AutoFilter.Range.Row, ActiveSheet.AutoFilter.Range.Column).Resize(iRowsAutofiltered, iNumAutofilters)
End Function
2
  • Why in the WORLD would someone downvote this useful answer, which even includes complete self-standing code (and heavily tested), and even gives a detailed explanation of the code? I'm asking, seriously. Do people want good professional help in superuser or should I not even bother? Are there trolls that just go downvoting, in order to relatively prop themselves up?? Aug 27, 2017 at 21:49
  • 2
    At least have the b___s to comment if you downvote, so I can use the feedback to better serve the community. Admins, that's a proposal I would like to make. Have all downvotes require an explanatory comment. I have seen many clearly great posts inexplicably downvoted, which really seems to be an epidemic in superuser - and nowhere else. Aug 27, 2017 at 21:50
-1

using "data, from table"/power query in excel, which gives us option to refresh data when opening file.
(also auto sort, and index column (number filtered rows automatically ))

This will create result in another sheet.

-select data required using mouse (rows and columns)
-click on data tab, from table
-in the last column, exclude blanks (optional, if you want to to display only filled cells)
-add column, index column (optional, if you want to add row number to filtered results)
-close and load to

to edit again, click on query tab, and then on edit

click on design tab in excel, on the arrow below refresh, connection properties,
refresh data when opening file.


adapted from: https://www.excelcampus.com/tips/sort-drop-down-lists-automatically/
part: 3. Sorting Drop Down Lists Using Power Query


you can also copy data from sheet1 if not empty, for example field a1.
copy this to a1 field in sheet2:
=IF(Sheet1!A1"";Sheet1!A1;"")

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .