1. Home
  2. MS Office

How To Auto-Refresh Filtered Data In Excel When It Is Updated

Microsoft Excel lets you filter data. It’s a useful feature that can help you make sense of large amounts of data. If you lack the technical know-how needed to create a database, this feature is a life saver. The filters are fairly basic and allow you to include or exclude data from a column. The filter extends to and applies to data values in the corresponding rows as well. What the filter doesn’t do is auto-update itself. If a value in a filtered column changes, either because you manually updated it or a formula is in use, you have to apply the filters again. A simple code snippet allows you to update filtered columns on the fly.

You will have to enable Macros and save your Excel file as a macro enabled file, .XLSM, in order to use the code. To save the file as a macro enabled file, go to File>Save As and select Excel Macro Enabled Workbook from the ‘Save As’ dialog box.

Next, select the sheet you have applied filters to. Right-click it and select ‘View code’ from the context menu.

A Microsoft Visual Basic window will open with the current Excel sheet. Paste the following code in the code window.

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

Save the file. If you didn’t save the file as an Excel Macro Enabled Workbook, you will be prompted to do so again. This file that you save will be saved a copy and will not overwrite the current file.

Once the code has been added, you can update any cell in a filtered column and it will auto-refresh filtered data as per the conditions you’ve set. This code was written by Stack Exchange user Sorin.

Leave a comment

  • brooks_erik@mail.ru

    This is one way it is possible to make a very decent wealth each month… You can Try it for you personally! After been without work for half a year, I began freelancing over this web site and finally i couldn’t be pleased. After six months on my latest job my each month pay is around 12k per 30 days…>> FACEBOOK.COM/For-US-UK-Canada-Australia-and-New-Zealand-1081278581977571/app/208195102528120/

  • This is how it is possible to make a quite wonderful wealth per 30 days… You can test it for you personally! After been without work for six months, I began freelancing over this website and at the moment i couldn’t be more joyful. After six months on my latest job opportunity my monthly pay is around 12k per 30 days…>>FACEBOOK.COM/For-UK-US-Australia-Canada-and-New-Zealand-1241554745928856/app/208195102528120/

  • kenneth.mcclendon@mail.ru

    I was paid 104,000 thousand dollars in 2016 by doing an on-line job a­n­d I was able to do it by working in my own time f­­o­­r 3 or sometimes more h /day. I was following work opportunity I came across from this website i found online and I am amazed that I was able to earn so much extra income. It’s very beginner-friendly a­­n­­d I am just so thankful that I found out about this. This is what i do… SHARE37.COM