SOLVED

Getting Table Filters to automatically update

%3CLINGO-SUB%20id%3D%22lingo-sub-1322782%22%20slang%3D%22en-US%22%3EGetting%20Table%20Filters%20to%20automatically%20update%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1322782%22%20slang%3D%22en-US%22%3E%3CP%3EHi%3C%2FP%3E%3CP%3EI%20have%20a%20table%20of%20data%20that%20stems%20from%20another%20block%20of%20data%2C%20some%20of%20which%20are%20blank%20rows.%26nbsp%3B%20The%20filter%20on%20the%20table%20is%20set%20up%20to%20not%20include%20blanks%20rows.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMy%20question%20is%2C%20Can%20I%20set%20the%20filter%20on%20the%20table%20to%20automatically%20update%20when%20the%20source%20data%20is%20updated%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20help%20with%20is%20would%20be%20greatly%20appreciated%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMany%20thanks%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EKeith%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1322782%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1323147%22%20slang%3D%22en-US%22%3ERe%3A%20Getting%20Table%20Filters%20to%20automatically%20update%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1323147%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F59521%22%20target%3D%22_blank%22%3E%40Keith%20Farmery%3C%2FA%3E%26nbsp%3Bhello%20there!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20could%20use%20a%20worksheet%20change%20event%20on%20your%20block%20of%20data.%20In%20that%20worksheets%20code%20module%20(right-click%20the%20sheet%20name%2C%20select%20'View%20Code')%2C%20paste%20this...%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-csharp%22%3E%3CCODE%3EPrivate%20Sub%20Worksheet_Change(ByVal%20Target%20As%20Range)%0A%0A%20%20%20%20Dim%20Table%20As%20ListObject%0A%20%20%20%20Dim%20DataChangeRange%20As%20Range%0A%20%20%20%20%0A%20%20%20%20Set%20Table%20%3D%20ThisWorkbook.Worksheets(%22Sheet1%22).ListObjects(1)%20'%20%26lt%3B%26lt%3B%20Set%20to%20your%20Table%20of%20data%20to%20filter%0A%20%20%20%20Set%20DataChangeRange%20%3D%20Me.Range(%22A1%3AA100%22)%20'%20%26lt%3B%26lt%3B%20Set%20to%20where%20your%20block%20of%20data%20resides%0A%20%20%20%20%0A%20%20%20%20If%20Not%20Intersect(Target%2C%20DataChangeRange)%20Is%20Nothing%20Then%0A%20%20%20%20%20%20%20%20Table.Range.AutoFilter%20Field%3A%3D1%2C%20Criteria1%3A%3D%22%26lt%3B%26gt%3B%22%0A%20%20%20%20End%20If%0A%20%20%20%20%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHTH%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1324101%22%20slang%3D%22en-US%22%3ERe%3A%20Getting%20Table%20Filters%20to%20automatically%20update%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1324101%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F10842%22%20target%3D%22_blank%22%3E%40Zack%20Barresse%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EZack%3C%2FP%3E%3CP%3EThanks%20for%20this.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI've%20inserted%20the%20code%2C%20but%20it%20is%20not%20working.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20not%20sure%20what%20I've%20done%20wrong.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECan%20I%20please%20clarify%3A-%3C%2FP%3E%3CPRE%3E%20Set%20Table%20%3D%20ThisWorkbook.Worksheets(%22Sheet1%22).ListObjects(1)%20'%20%26lt%3B%26lt%3B%20Set%20to%20your%20Table%20of%20data%20to%20filter%0A%20%20%20%20Set%20DataChangeRange%20%3D%20Me.Range(%22A1%3AA100%22)%20'%20%26lt%3B%26lt%3B%20Set%20to%20where%20your%20block%20of%20data%20resides%3C%2FPRE%3E%3CP%3EMy%20worksheet%20is%20called%20%22Journal%22%20and%20my%20Table%20is%20also%20called%20%22Journal%22%2C%20the%20data%20that%20would%20trigger%20an%20update%20to%20my%20table%20is%20an%20area%20called%20%22Revised_Balance%22%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20is%20the%20following%20correct%3F%3C%2FP%3E%3CP%3ESet%20Table%3DThisWorkBook.Worksheets(%22Journal%22).ListObjects(1)%3C%2FP%3E%3CP%3ESet%20DataChangeRange%20%3DMe.Range(%22Revised_Balance%22)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20for%20your%20help%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EKeith%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1325301%22%20slang%3D%22en-US%22%3ERe%3A%20Getting%20Table%20Filters%20to%20automatically%20update%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1325301%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F59521%22%20target%3D%22_blank%22%3E%40Keith%20Farmery%3C%2FA%3E%26nbsp%3Byes.%20The%20code%20needs%20to%20go%20in%20the%20sheet%20module%20where%20%22Revised_Balance%22%20is%20housed.%20So%20you%20could%20use%20this...%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-csharp%22%3E%3CCODE%3ESet%20Table%3DThisWorkBook.Worksheets(%22Journal%22).ListObjects(%22Journal%22)%0ASet%20DataChangeRange%20%3DMe.Range(%22Revised_Balance%22)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWorks%20for%20me%20in%20my%20tests.%20If%20it's%20still%20not%20working%20let%20me%20know%2C%20perhaps%20you%20can%20upload%20your%20file.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1325670%22%20slang%3D%22en-US%22%3ERe%3A%20Getting%20Table%20Filters%20to%20automatically%20update%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1325670%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F10842%22%20target%3D%22_blank%22%3E%40Zack%20Barresse%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20-%20I'll%20give%20it%20a%20go%20-%20later%20tonight%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Contributor

Hi

I have a table of data that stems from another block of data, some of which are blank rows.  The filter on the table is set up to not include blanks rows.

 

My question is, Can I set the filter on the table to automatically update when the source data is updated?

 

Any help with is would be greatly appreciated

 

Many thanks

 

Keith

4 Replies
Highlighted

@Keith Farmery hello there!

 

You could use a worksheet change event on your block of data. In that worksheets code module (right-click the sheet name, select 'View Code'), paste this...

 

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim Table As ListObject
    Dim DataChangeRange As Range
    
    Set Table = ThisWorkbook.Worksheets("Sheet1").ListObjects(1) ' << Set to your Table of data to filter
    Set DataChangeRange = Me.Range("A1:A100") ' << Set to where your block of data resides
    
    If Not Intersect(Target, DataChangeRange) Is Nothing Then
        Table.Range.AutoFilter Field:=1, Criteria1:="<>"
    End If
    
End Sub

 

HTH

Highlighted

@Zack Barresse 

Zack

Thanks for this.

 

I've inserted the code, but it is not working.

 

I'm not sure what I've done wrong.

 

Can I please clarify:-

 Set Table = ThisWorkbook.Worksheets("Sheet1").ListObjects(1) ' << Set to your Table of data to filter
    Set DataChangeRange = Me.Range("A1:A100") ' << Set to where your block of data resides

My worksheet is called "Journal" and my Table is also called "Journal", the data that would trigger an update to my table is an area called "Revised_Balance"

 

So is the following correct?

Set Table=ThisWorkBook.Worksheets("Journal").ListObjects(1)

Set DataChangeRange =Me.Range("Revised_Balance")

 

Thanks for your help

 

Keith

Highlighted
Best Response confirmed by Keith Farmery (Occasional Contributor)
Solution

@Keith Farmery yes. The code needs to go in the sheet module where "Revised_Balance" is housed. So you could use this...

 

Set Table=ThisWorkBook.Worksheets("Journal").ListObjects("Journal")
Set DataChangeRange =Me.Range("Revised_Balance")

 

Works for me in my tests. If it's still not working let me know, perhaps you can upload your file.

Highlighted

@Zack Barresse 

 

Thanks - I'll give it a go - later tonight