SOLVED

Excel Filters

%3CLINGO-SUB%20id%3D%22lingo-sub-2554262%22%20slang%3D%22en-US%22%3EExcel%20Filters%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2554262%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20using%20a%20filter%20to%20limit%20how%20much%20data%20is%20shown%2C%20however%20when%20the%20data%20changes%20i%20would%20like%20it%20to%20be%20displayed.%3C%2FP%3E%3CP%3EEX%3A%20the%20filters%20is%20blocking%20all%20rows%20that%20have%20'0'%20in%20them.%26nbsp%3B%20when%20the%20value%20of%20that%20cell%20is%20changed%20to%20'5'%20I%20would%20like%20it%20to%20be%20shown%20in%20the%20list.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20VBA%20to%20automatically%20reapply%20filters%20when%20data%20is%20changed%20but%20it%20only%20seems%20to%20work%20for%20taking%20away%20data.%26nbsp%3B%20Is%20there%20a%20way%20to%20make%20it%20so%20new%20data%20is%20automatically%20shown%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2554262%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2555213%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Filters%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2555213%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1104790%22%20target%3D%22_blank%22%3E%40kbradbury%3C%2FA%3E%26nbsp%3BNow%2C%20I'm%20no%20VBA%20expert%2C%20but%20when%20you%20include%20a%20line%20of%20code%20like%20below%2C%20it%20should%20work%20just%20fine.%20Every%20time%20the%20code%20is%20run%20it%20will%20re-apply%20the%20filter%2C%20i.e.%20exclude%20all%20zeros.%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3EActiveSheet.Range(%22%24A%241%22).AutoFilter%20Field%3A%3D2%2C%20Criteria1%3A%3D%22%26lt%3B%26gt%3B0%22%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E...where%20%C2%A7A%C2%A71%20is%20assumed%20to%20be%20the%20top%20left%20hand%20cell%20for%20the%20data%20table%20and%20the%202nd%20column%20(%3CEM%3EField%3A%3D2%3C%2FEM%3E)%20is%20the%20one%20to%20filter.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2570171%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Filters%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2570171%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3BI%20ended%20up%20recording%20a%20macro%20for%20applying%20a%20not%20equal%20to%200%20filter.%26nbsp%3B%20I%20think%20the%20vba%20code%20came%20out%20basically%20the%20same%20though!%26nbsp%3B%20Thanks%20for%20taking%20the%20time%20to%20respond.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New 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 (New 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.