Removing #DIV/0! Error from 791 Sheets together

mhenk

Well-known Member
Joined
Jun 13, 2005
Messages
591
I have a number of automatically generated sheets. On a number of them, there are #DIV/0! Errors. I would like to replace these #DIV/0! Errors with Blank cells, or, at the very least, change the formatting of these #DIV/0! Errors so that they don't print (i.e. are white)

I have 791 of these sheets, is there any quick method you can suggest to do this?
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,705
Try the following...

1) Click the name tab for the first sheet

2) Shift-Click the name tab for the last sheet

3) Edit > Replace

Find what: #DIV/0!

Replace with: (leave blank)

4) Click Replace All

5) CONTROL+Clik any sheet tab

6) Select 'Ungroup Sheets'

Hope this helps!
 

tvkrys

Board Regular
Joined
Jan 23, 2006
Messages
135
mhenk

Unless later versions of Excel work differently than 2000 does (and you happen to have that version), Domenic's suggestion won't work because the replace function is looking for "text" that says "#DIV/0!" not the results of the formula yielding and error message that displays those characters... does that make sense?

Unfortunately, I don't have enough programming skills to help you with your existing 791 sheets. If a more experienced member can't devise a fix for you, I can at least suggest a solution to prevent it in the future. I don't know how extensive your formulas are throughout the existing template, so this might not be pracitical. But, if it is, you can alter both the format of the cells and the formulas themselves to produce the desired results in the future.

For example:

Let's say the current formula in A1 is "=B1/C1". If C1 is empty or equals zero, then A1 displays " #DIV/0! " because you can't divide by zero or nothing. What you want to do is force the formula to always result in zero and then format the cell to be blank whenever it does.

If you change the formula to "=IF(C1,B1/C1,0)", A1 will always have a number displayed. If it's a real number then it's the real number, if it's a number divided by zero or nothing, the result will be zero.

If you don't want the zero to be displayed, the next step is to change the number format. I don't know what number format you're using but let's say positive numbers are black and display as 00.00 and negative numbers are (00.00) and red in color. Your custom format to have zeros displayed as a blank cell would be: 00.00;[Red](00.00);

To change your format, click Format > Cells > Number... Choose Custom for the Category and in Type, type "00.00;[Red](00.00);"

I prefer to have a hyphen displayed, rather than a blank cell (so I am certain the cell still has a formula in it and is not empty). So my custom format would be:
"00.00;[Red](00.00);-"

But again, you'll have to determine how time effective this revision this would be for you. The new formula is dynamic so can be cut/pasted, filled, dragged, etc. like a normal formula and any text combination can be added after the third ";" in the custom format to replace a zero... I have some that say "n/a", "NONE", and for percentages, "EVEN".

Hope this helps... Krys

P.S. As always, don't include the quotation marks when entering the above text strings.
 

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,705
Unless later versions of Excel work differently than 2000 does (and you happen to have that version), Domenic's suggestion won't work ...

Actually, I'm using a Mac version of Excel, in which it seems to work. I thought it would be the same for Windows versions of Excel. I guess that's not the case...
 

mhenk

Well-known Member
Joined
Jun 13, 2005
Messages
591

ADVERTISEMENT

I managed to work around the situation by setting up conditional formatting on all the sheets to change the font of the errors. It was not feasible for me to change the formulas as you recommended Krys, but thanks for your suggestion.
 

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,705
It looks like I missed the crucial point made by Krys -- the error values are a result of formulas, not text. :oops:
 

tvkrys

Board Regular
Joined
Jan 23, 2006
Messages
135

ADVERTISEMENT

I'm not that familiar with Conditional Formatting... what was your solution? Krys
 

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
format | condition format | formula is:

=error.type(a1)=2

...set font to white (assuming white background)
 

tvkrys

Board Regular
Joined
Jan 23, 2006
Messages
135
Paddy... This doesn't seem to work for me... Tried it twice to be sure... K-
 

sunnyland

Well-known Member
Joined
Jan 27, 2006
Messages
912
Hello, there

Not sure you meant 791 sheets into one workbook, if so how many RAM your computer must have???

This is not the point .

You can try this code supposing your sheets are in the same workbook. Or even you can use this code on any workbook really.

Place this code on the workbook. When a sheet is activated by default the error font will return to black. Before printing 1 worksheet, the font will become white.

I am supposing you are not printing the whole workbook at once but rather printing individual worksheet.

Macro backtoblack or backtowhite can be run manually if prefered: if you prefer then remove code in before print and activate event. That will save some overhead depending on your computer speed.

Have a try and see.

Running backtowhite and backtoblack manually will reduce the overhead of conditional formatting if you have a lot of cell with formula and seing that there is an error is always a good idea as suggested by Kris.

Code:
Sub CHANGEFONTCOLORIFERROR(Optional whitenotblackfont As Boolean )
'error catching in case there is no formula on the active sheet
On Error Resume Next
ActiveSheet.Cells.SpecialCells(xlCellTypeFormulas, 16).Select
If Err.Number = 0 Then
If whitenotblackfont = True Then
Selection.Font.COLOR = vbWhite
Else
Selection.Font.COLOR = vbBlack
End If
End If

End Sub



Private Sub Workbook_BeforePrint(Cancel As Boolean)
'before printing will change font to white
CHANGEFONTCOLORIFERROR True


End Sub

Sub backtoblack()
'return error formula to black font for the active sheet if you want to run the macro manually
CHANGEFONTCOLORIFERROR
End Sub
Sub backtowhite()
'return error formula to white font for the active sheet if you want to run the macro manually
CHANGEFONTCOLORIFERROR True

End Sub

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
'will change
CHANGEFONTCOLORIFERROR
End Sub
 

Forum statistics

Threads
1,136,993
Messages
5,679,023
Members
419,800
Latest member
spvsr999

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