Forum Discussion

Keith Farmery's avatar
Keith Farmery
Copper Contributor
Apr 20, 2020
Solved

Getting Table Filters to automatically update

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

excel
Formulas and Functions
Macros and VBA
  • Zack Barresse's avatar
    Zack Barresse
    Apr 21, 2020

    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.

4 Replies

  • Zack Barresse's avatar
    Zack Barresse
    Iron Contributor
    Apr 20, 2020

    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

    • Keith Farmery's avatar
      Keith Farmery
      Copper Contributor
      Apr 21, 2020

      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

      • Zack Barresse's avatar
        Zack Barresse
        Iron Contributor
        Apr 21, 2020

        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.

Resources