SOLVED

Excel Filters

Copper Contributor

I am using a filter to limit how much data is shown, however when the data changes i would like it to be displayed.

EX: the filters is blocking all rows that have '0' in them.  when the value of that cell is changed to '5' I would like it to be shown in the list.  

 

I have VBA to automatically reapply filters when data is changed but it only seems to work for taking away data.  Is there a way to make it so new data is automatically shown?

2 Replies
best response confirmed by kbradbury (Copper Contributor)
Solution

@kbradbury Now, I'm no VBA expert, but when you include a line of code like below, it should work just fine. Every time the code is run it will re-apply the filter, i.e. exclude all zeros.

ActiveSheet.Range("$A$1").AutoFilter Field:=2, Criteria1:="<>0"

...where §A§1 is assumed to be the top left hand cell for the data table and the 2nd column (Field:=2) is the one to filter.

@Riny_van_Eekelen I ended up recording a macro for applying a not equal to 0 filter.  I think the vba code came out basically the same though!  Thanks for taking the time to respond.

1 best response

Accepted Solutions
best response confirmed by kbradbury (Copper Contributor)
Solution

@kbradbury Now, I'm no VBA expert, but when you include a line of code like below, it should work just fine. Every time the code is run it will re-apply the filter, i.e. exclude all zeros.

ActiveSheet.Range("$A$1").AutoFilter Field:=2, Criteria1:="<>0"

...where §A§1 is assumed to be the top left hand cell for the data table and the 2nd column (Field:=2) is the one to filter.

View solution in original post