Delete the contents of a cell if it contains #DIV/0! Automaticall.

[GoodyA10]

I am building an execl sheet that holds production data. Some pieces of
equipment only run occasionally and because of this they cause a #DIV/0!
error. I want to be able to look through the complete range (a1:di54) of
cells and whenver I find the error i want to delete the contents of the cell.
I guess this is possible to do using a Visual Basic module but my experience
of Visual Basic is limited.

 

[Jacob Skaria]

Its better to handle the error within the formula itself.

If =A1/B1 returns an error change the formula to

=IF(N(B1),A1/B1,"")


You can try out the below macro. If you are new to macros..

--Set the Security level to low/medium in (Tools|Macro|Security).
--From workbook launch VBE using short-key Alt+F11.
--From menu 'Insert' a module and paste the below code.
--Get back to Workbook.
--Run macro from Tools|Macro|Run <selected macro()>


Sub Macro()
Dim cell As Range
For Each cell In ActiveSheet.UsedRange
If cell.Text = "#DIV/0!" Then cell.ClearContents
Next
End Sub

 

[Gary''s Student]

Try this simple macro:

Sub ClearErrors()
Dim r As Range
Set r = Range("A1I54").Cells.SpecialCells(xlCellTypeFormulas, xlErrors)
r.Clear
End Sub



Macros are very easy to install and use:

1. ALT-F11 brings up the VBE window
2. ALT-I
ALT-M opens a fresh module
3. paste the stuff in and close the VBE window

If you save the workbook, the macro will be saved with it.

To remove the macro:

1. bring up the VBE window as above
2. clear the code out
3. close the VBE window

To use the macro from Excel:

1. ALT-F8
2. Select the macro
3. Touch RUN

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[GoodyA10]

Thanks for that both solutions did the trick.

Cheers

 

[GoodyA10]

Thanks for the reply

Try this simple macro:

Sub ClearErrors()
Dim r As Range
Set r = Range("A1I54").Cells.SpecialCells(xlCellTypeFormulas, xlErrors)
r.Clear
End Sub



Macros are very easy to install and use:

1. ALT-F11 brings up the VBE window
2. ALT-I
ALT-M opens a fresh module
3. paste the stuff in and close the VBE window

If you save the workbook, the macro will be saved with it.

To remove the macro:

1. bring up the VBE window as above
2. clear the code out
3. close the VBE window

To use the macro from Excel:

1. ALT-F8
2. Select the macro
3. Touch RUN

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm