Suppressing Zeros Macro

austinda

New Member
Joined
Aug 3, 2016
Messages
12
EfNFjWnRnoVKiYRQSu2mZr4BWxDZ--_OB2YinSkBfWrHCw
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
EfNFjWnRnoVKiYRQSu2mZr4BWxDZ--_OB2YinSkBfWrHCw

EfNFjWnRnoVKiYRQSu2mZr4BWxDZ--_OB2YinSkBfWrHCw
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

Special-K99

Well-known Member
Joined
Nov 7, 2006
Messages
8,425
Office Version
  1. 2019
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:
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,297
Messages
5,836,479
Members
430,434
Latest member
whatabout

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top