Apr 20 2020 01:57 PM
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
Apr 20 2020 04:49 PM
@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
Apr 21 2020 01:48 AM
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
Apr 21 2020 08:50 AM
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.
Apr 21 2020 09:46 AM
Apr 21 2020 08:50 AM
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.