Is it possible to set auto-refesh for column which has a auto filter in Excel

[RopeME]

Sponsored Links

I am working in Excel, and have an issue with filtering a column automatically. The column is fed by formulas in the cells. I want to auto filter the column (non-blanks) and this works fine. However, when the input data is changed, and the column fills with new values, the filter is not automatically re-applied, and I have to manually re-apply it. How can I atomize this process.

[Hollyn]

This example uses data in column A. The macro will automatically refresh the autofilter whenever formulas are calculated:

Private Sub Worksheet_Calculate()
Application.EnableEvents = False
Set r = ActiveCell
Columns("A:A").Select
Application.CutCopyMode = False
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:="<>"
r.Select
Application.EnableEvents = True
End Sub

Note that this is Event code and goes in the worksheet code area, not a standard module.

[QUENBY]

There are some codes that might work. Complete the first Workbook_Open-/Workbook_BeforeClose - Events with these lines:

 Private Sub Workbook_Open (Cancel As Boolean) 
 Call Terminate_Class 
 End Sub 

 Private Sub Workbook_Open () 
 Call Init_Class 
 End Sub

Then on a standard module use the following code:

 Option Explicit 
 Public gblnRefreshInProgress As Boolean 
 As a private lobjQueryTableClass clsQueryTable 
 Public Sub Init_Class () 
 Set lobjQueryTableClass = New clsQueryTable 
 Set lobjQueryTableClass.prpQueryTable = Tabelle1.QueryTables 
 End Sub 

 Public Sub Terminate_Class () 
 Set lobjQueryTableClass = Nothing 
 End Sub