Fixing a script to delete N/As in large data sets

  • Hi All -


    I am using the script below to look at column A and if N/A is present it will delete that entire row of data. I'm using this script on a data set that has roughly 400-500k rows and when I run it, the script just deletes everything. When the script is used on smaller data sets ~ a dozen or so it works fine. Could you let me know how to fix this?


  • Re: Fixing a script to delete N/As in large data sets


    Give this a try:


    Have a Great Day!

  • Re: Fixing a script to delete N/As in large data sets


    Thanks so much!


    Is there a way to make this faster? Using it on over 300k lines takes a while!

  • Re: Fixing a script to delete N/As in large data sets


    This will be faster, but it will remove all errors, not just #N/A's:


    Code
    Sub SuperKiller()
        Dim r As Range
        Set r = Range("A:A").Cells.SpecialCells(xlCellTypeFormulas, xlErrors)
        r.EntireRow.Delete
    End Sub

    Have a Great Day!

  • Re: Fixing a script to delete N/As in large data sets


    This will only delete #N/A errors:



    Regards,


    Robert

  • Re: Fixing a script to delete N/As in large data sets


    Hi Hoowzer,


    Thanks for the feedback and you're welcome :)


    Please note though not to re-quote entire posts as it just creates clutter.


    Regards,


    Robert

Participate now!

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