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.
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.
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
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! ;)
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
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: https://msdn.microsoft.com/en-us/library/office/ff193884.aspx
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):
Save the range of the autofilter. It has .AutoFilter.Filters.Count columns, and (.AutoFilter.Range.Count/.AutoFilter.Filters.Count) rows, saved to rngAutofilter
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)
Turn off the filter but preserve the dropdowns with .ShowAllData
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
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;"")