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

7 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

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:

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! ;)

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

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:

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 (.) 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

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:
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;"")

Your Answer

Sign up or log in

Sign up using Google Sign up using Facebook Sign up using Email and Password Submit

Post as a guest

Post Your Answer Discard

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy