DELETE CELLS w/ "DIV/0" MACRO

[Tim H]

Trying to write a macro that will do a 'search and delete' type function for
cells that yield a "DIV/0" error.

What I want to accomplish is a break in the line graph when there is no
data. Problem is that excel 2003 treats anything in a cell other than a
numerical value as a ZERO. Even if I write an IF(ISERROR) function to yield
"", it still treats the cell as a zero value.

The only way to get a break in the line is to actually delete out the
formula completely when there's a "DIV/0" error.

Any suggestions?

 

[Andy Pope]

Hi,

If your formula are in the range C4:C10 then,

Sub ClearFormulaErrors()
On error resume next
range("C4:C10").SpecialCells(xlCellTypeFormulas,xlErrors).Clear
End Sub

Instead of "" use NA(). This will not create a gap but will stop if being
treated as zero.

Cheers
Andy

 

[Tim H]

The NA() is perfect!!!

Thanks.

Hi,

If your formula are in the range C4:C10 then,

Sub ClearFormulaErrors()
On error resume next
range("C4:C10").SpecialCells(xlCellTypeFormulas,xlErrors).Clear
End Sub

Instead of "" use NA(). This will not create a gap but will stop if being
treated as zero.

Cheers
Andy

 

[Tim H]

Andy,

The NA() only works when there is data missing at the end of the chart
range. If there's an NA() in the middle of the range, excel charts basically
continue the last point of data out until there is new data instead of
putting in breaks in the line.

So, given your code below to actually clear the error formula cells, how do
I adjust the code to start at a given point and read until there is no more
data. In other words, every time I run the report, the range will change.
Range may be C4:C10 this week, but can be C4:C15 next week.

How do I alter the code below to read for data starting from C4 until the
end of available data?

Thanks,

 

[Andy Pope]

Try this variation.

Sub ClearFormulaErrors()
On Error Resume Next
Range(Cells(4, 3), Cells(Rows.Count, 3).End(xlUp) _
).SpecialCells(xlCellTypeFormulas, xlErrors).Clear
End Sub

Generates a range for C4 to last value in column C

Cheers
Andy