Conditional Text Deletion From Cells

Important Notice


Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.

  • 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'.


  • 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
  • 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

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

  • 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.

  • Re: Conditional text deletion from cell


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

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!