code to clearcontents of "0" values in cells

  • Thread starter Thread starter friesianet
  • Start date Start date
F

friesianet

This should be simple, but I'm just not getting it. There are only two
ranges I care about Range("J:J") and Range("K:K"). There are many
numeric values in the cells in these columns, but I just want to run a
macro to delete all the "0" values. I've muddled in this, and the
closest I could get it tested the value with an if statement and then,
when it found the range true to have a "0" within the column, it
cleared the contents of the whole column.

Please help! I just need to delete the cells that have zeros in them
so I can run average totals at the bottom of the columns. Obviously, I
don't want to average in the zeros!

Thanks,
LeeCB
 
You could cycle the range like this:

For i = 0 to 1
if i = 0 then
Set SearchRange = ActiveSheet.Range("J1:J" &
ActiveSheet.Range("J65536").End(xlup).Row)
else
Set SearchRange = ActiveSheet.Range("K1:K" &
ActiveSheet.Range("K65536").End(xlup).Row)
End If
For Each Cell in SearchRange
If Cell.Value = 0 then Cell.ClearContents
Next Cell
Next i

tod
 
Back
Top