Clear cells resulting in div/0 or zero ?

[Blewyn]

How do I code a routine to clear cells which result in DIV/0 or zero ?

So far I have :

Cells(Count, colcount).Activate
Check_for_Null = IsNull(ActiveCell.Value)
If (Check_for_Null = True) Then ActiveCell.ClearContents
Zero_check = Str(ActiveCell.Value)
If Zero_check = "0" Then ActiveCell.ClearContents

but I keep getting type mismatch...

Is there a better way to do this ?

Thanks,

Blewy

 

[Frank Kabel]

Hi
to test for a #DIV/0 error you may sue something like
on error resume next
if activecell.value = CVErr(2007) then
activecell.clearcontents
end if
on error goto 0

 

[Blewyn]

Thanks Frank, but I've found a much easier way :

copy all the cells
paste special to new area as values only
delete originals
search/replace zero with empty
search/replace #DIV/0! with empty

of course this is no good if you want to keep the original cell
including their formulae.

Cheers,

Blewy

 

[Frank Kabel]

Hi
another idea:
why don't you change your formulas to something like
=IF(Divisor=0,"",some_value/Divisor)
This way you can keep the formulas but hide the #DIV/0 errors

 

[Blewyn]

Great ! That's a much more elegant solution.

Cheers,

Blewy