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.