Clear cell if equals certain value

**clwrs** · 08-17-2009, 01:54 PM

I am trying to learn how I can program into a macro the function of clearing, and not deleting, a cell in column k, n and o if it is equal to a cerain value. These values would be #N/A, 1/0/1900 and 0 respectively. Thank you for your help.

**shua79** · 08-17-2009, 02:20 PM

For N/A I would use the following


 If iserror(sheets("myworksheet").range("K1").value) Then sheets("Myworksheet").range("K1").clearcontents

for the other two its basically the same

 if (Sheets("myworksheet").range("N1").value = 0) Then sheets("Myworksheet").range("N1").clearcontents

Hope this helps

**JBeaucaire** · 08-17-2009, 04:27 PM

Another approach that does more than one row at a time:

Option Explicit

Sub Deletions()
Dim Rng As Range, cell As Range

'Delete errors all at once in the entire column(s)
    Range("K:K,N:O").SpecialCells(xlCellTypeFormulas, 16).ClearContents

'Evaluate cells and delete blanks and zeros
    Set Rng = Range("K:K,N:O").SpecialCells(xlCellTypeFormulas)
        For Each cell In Rng
            If cell.Value = 0 Then cell.ClearContents
        Next cell
    Set Rng = Nothing

End Sub