Suppressing Zeros Macro

austinda

New Member
Joined
Aug 3, 2016
Messages
12
I need help suppressing zeros on my worksheet. I created a formula
Code:
=IF(ISBLANK(B8),"x",IF(OR(D8<>0,E8<>0,F8<>0,G8<>0,H8<>0,I8<>0,J8<>0,K8<>0,L8<>0,M8<>0,N8<>0,O8<>0,P8<>0,Q8<>0),"x",""))
, then a macro that would apply the filter.

Code:
Sheets("Forecast Detail").Select
    ActiveSheet.Range("$S$7:$S$500").AutoFilter Field:=1, Criteria1:="x"

Then another macro to unfilter it
Code:
Sheets("Forecast Detail").Select
ActiveSheet.Range("$S$7:$S$415").AutoFilter Field:=1

This all works ok as the don't mess things up by doing one of the following
1. user carries formula down when adding rows
2. Add or change the filters
3. Add additional data in the column

Sometimes it just fails and filters everything

I was hoping i could get some tip, trick on how I can make this more bulletproof

1. I only want to suppress zeros in the blue sections,
2. If the entire blue section is all zero suppress the entire section, including the totals
3. I would like the user to be able to add filter if they want to

Any advice would be helpful

 

Some videos you may like

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

Special-K99

Well-known Member
Joined
Nov 7, 2006
Messages
8,350
This doesnt answer your question but why not change this

Code:
=IF(ISBLANK(B8),"x",IF(OR(D8<>0,E8<>0,F8<>0,G8<>0,H8<>0,I8<>0,J8<>0,K8<>0,L8<>0,M8<>0,N8<>0,O8<>0,P8<>0,Q8<>0),"x",""))

to

Code:
=IF(ISBLANK(B8),"x",IF(SUM(D8:Q8),"x",""))
 
Last edited:

austinda

New Member
Joined
Aug 3, 2016
Messages
12
Thanks, great question, I had started the design with that
Code:
[COLOR=#333333]IF(SUM(D8:Q8)[/COLOR]
but I had some lines that would have a positive number in one period and negative in the next so I didn't want suppress that line.
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,109,321
Messages
5,528,001
Members
409,798
Latest member
Snake68

This Week's Hot Topics

Top