Results 1 to 3 of 3

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

  1. #1
    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.

  2. #2
    Join Date
    Jul 2011
    Posts
    642

    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.

  3. #3
    Join Date
    Jun 2011
    Posts
    641

    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

Similar Threads

  1. How to Auto Filter in Excel
    By Lanka Boy in forum Windows Software
    Replies: 2
    Last Post: 07-01-2012, 08:17 PM
  2. Replies: 4
    Last Post: 05-09-2011, 11:03 AM
  3. Auto-Tag whole library with Winamp Auto-tagger
    By JAMIN in forum Windows Software
    Replies: 3
    Last Post: 08-07-2009, 12:05 PM
  4. Auto hide rows and column in Excel 2007
    By Murena in forum Windows Software
    Replies: 3
    Last Post: 28-02-2009, 06:00 PM
  5. Auto Logon AND auto lock workstation?
    By Nadeem in forum Windows XP Support
    Replies: 3
    Last Post: 09-11-2006, 04:49 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •