making macro that deletes cells w values of 0

[Runner23]

Hi,

I need to make a macro that erases cells with the value 0 across 10-12 columns?

Can anyone help me do this?

Much obliged

 

[Peter_SSs]

Welcome to the MrExcel board!

Do you really need a macro? This could fairly quickly be done manually.

If you do need a macro, here is one that erases 0 cells from the first 12 columns. It assumes the zero values are not the result of formulas. If that is not the case post back with more details.

I suggest that you test in a copy of your workbook.

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> Erase_Zeros()<br>    Columns("A:L").Replace What:=0, Replacement:="", _<br>        LookAt:=xlWhole, SearchFormat:=False, ReplaceFormat:=False<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

 

[Runner23]

the zeros i get are results of formulas. Not sure how this would change things.

Also, afaik, if the zeros are replaced with " " , this is still interpreted as a zero in, for instance, a graph or chart.

 

[Peter_SSs]

If you are trying to deal with zeros because of a chart issue, try replacing your formulas with

=IF(existing_formula=0,NA(),existing_formula)

 

[Runner23]

If you are trying to deal with zeros because of a chart issue, try replacing your formulas with

=IF(existing_formula=0,NA(),existing_formula)

this formula did help me out, but i think i still need the macro to take out all the zeros.

The if statement you suggested gives me an NA when the formula equals zero, BUT since i use the resulting field for another calculation and a subsequent chart, I cannot have NA fields

 

[Peter_SSs]

Try this then.

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> Erase_Zeros()<br>    <SPAN style="color:#00007F">Dim</SPAN> ZeroFound <SPAN style="color:#00007F">As</SPAN> Range<br>    <br>    Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br>    <SPAN style="color:#00007F">With</SPAN> Columns("A:L")<br>        <SPAN style="color:#00007F">Set</SPAN> ZeroFound = .Find(What:=0, LookIn:=xlValues, _<br>            LookAt:=xlWhole, SearchFormat:=False)<br>        <SPAN style="color:#00007F">Do</SPAN> <SPAN style="color:#00007F">While</SPAN> <SPAN style="color:#00007F">Not</SPAN> ZeroFound <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN><br>            ZeroFound.ClearContents<br>            <SPAN style="color:#00007F">Set</SPAN> ZeroFound = .FindNext<br>        <SPAN style="color:#00007F">Loop</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>