Go Back   TechArena Community > Technical Support > Computer Help > Office Help > MS Office Support
Become a Member!
Forgot your username/password?
Register Tags Active Topics RSS Search Mark Forums Read

Sponsored Links



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

MS Office Support


Reply
 
Thread Tools Search this Thread
  #1  
Old 17-02-2012
Member
 
Join Date: Oct 2011
Posts: 119
Is it possible to set auto-refesh for column which has a auto filter in Excel

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.

Reply With Quote
  #2  
Old 17-02-2012
Member
 
Join Date: Jul 2011
Posts: 631
Re: Is it possible to set auto-refesh for column which has a auto filter in Excel

This example uses data in column A. The macro will automatically refresh the autofilter whenever formulas are calculated:
Code:
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.
Reply With Quote
  #3  
Old 17-02-2012
Member
 
Join Date: Jun 2011
Posts: 632
Re: Is it possible to set auto-refesh for column which has a auto filter in Excel

There are some codes that might work. Complete the first Workbook_Open-/Workbook_BeforeClose - Events with these lines:
Code:
 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:
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
Reply With Quote
Reply

  TechArena Community > Technical Support > Computer Help > Office Help > MS Office Support
Tags: , , ,



Thread Tools Search this Thread
Search this Thread:

Advanced Search


Similar Threads for: "Is it possible to set auto-refesh for column which has a auto filter in Excel"
Thread Thread Starter Forum Replies Last Post
How to Auto Filter in Excel Lanka Boy Windows Software 2 07-01-2012 08:17 PM
Difference between Auto Silent and Auto Profiles Manager in iphone KaIilAshhh Portable Devices 4 05-09-2011 11:03 AM
Auto-Tag whole library with Winamp Auto-tagger JAMIN Windows Software 3 08-07-2009 12:05 PM
Auto hide rows and column in Excel 2007 Murena Windows Software 3 28-02-2009 06:00 PM
Auto Logon AND auto lock workstation? Nadeem Windows XP Support 3 09-11-2006 04:49 AM


All times are GMT +5.5. The time now is 12:53 AM.