Conditional Text Deletion From Cells

JoePie · May 13th 2008

I'm getting a type mismatch (Runtime Error 13) with this code. What I want it to do is delete the contents of the cell if it contains 'N/A'.

Code

Sub RemoveNAs()


Dim word
word = "N/A"


    For Across = 1 To 5
        For Down = 2 To 150
            Cells(Down, Across).Select
            If Cells(Down, Across) = word Then
                Cells(Down, Across) = ""
            End If
        Next Down
    Next Across


End Sub

Display More

Simon Lloyd · May 13th 2008

Re: Conditional text deletion from cell

Better to use excels find and replace, far more efficient!

Code

Sub Clear_Found_Cells()


    Cells.Replace What:="N/A", Replacement:="", LookAt:=xlPart, SearchOrder:= _
        xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
End Sub

**rory** · May 13th 2008

Re: Conditional text deletion from cell

Do the cells actually contain #N/A? That would cause the Type Mismatch error. If so, change the "N/A" in Simon's code to "#N/A"
Regards,
Rory

Simon Lloyd · May 13th 2008

Re: Conditional text deletion from cell

Rory i assumed it wasn't #N/A as thats the result of a formula that hasn't got any alues to work with and can be cured at sheet level with the ISNA function within the formula.

**rory** · May 13th 2008

Re: Conditional text deletion from cell

Simon,
They might also be left over from a copy/paste special operation?
Regards,
Rory

Conditional Text Deletion From Cells

Participate now!

Share