Forum Discussion

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

excel

Formulas and Functions

Macros and VBA

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.

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

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.

Keith Farmery
Copper Contributor
Apr 21, 2020
Zack Barresse

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