Is there a way to protect a whole worksheet in excel from "Div/0"?

Question

I understand that you can protect the "Div/0" from coming up by using an if statement like If (B3=0, " ",A3/B3), but I've already made a huge worksheet without that incorporated into it. Is there a way I can change the "Div/0"s to something else? To use the if statement would take hours to do, as I did not originally put that into place.

Answer 1

I'd like to say that I solved this problem, but rather than re-inventing the wheel, I've found a copy of a macro code that should work for you.

Check out the below link for 2 different macros that should work. The macro codes are near the bottom of the thread.

In order to insert them into your workbook, simply go go to Tools >Macros > Visual Basic Editor, then right click "VBA Project (Filename)" and click Insert > Module. Double Click "Module 1" and paste the appropriate code into box.

To run the macro, click Tools > Macro > Macros, and then click run. you can also make the macro a hot-key by clicking properties in that box.

Answer 2

Incorporating additional 'if then' formulas in a huge complicated spreadsheet may not be a good idea. Who know what unseen bugs you unwittingly let loose by having a macro do a brute search and replace.

You may try this. Not really an elegant solution though. YMMV.

Select cell (a1). Next Select all the cells by clicking the top left corner.
Select conditional formatting. Select formula is =iserr(a1)
Select format. Select white for font color.
All the #errors will now be white. White on White = Invisible.