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

Hoowzer · Mar 20th 2014

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?

Code

Option Explicit


Sub GetRid()
    Range("A1", Range("A" & Rows.Count).End(xlUp)).AutoFilter 1, "#N/A"
    Range("A2", Range("A" & Rows.Count).End(xlUp)).EntireRow.Delete
    [a1].AutoFilter
End Sub




Sub GetRid2()
Dim lr As Long
lr = Range("A" & Rows.Count).End(xlUp).Row




    Range("A1", Range("A" & Rows.Count).End(xlUp)).AutoFilter 1, "#N/A"
    lr = Range("A" & Rows.Count).End(xlUp).Row
    If lr > 1 Then Range("A2", Range("A" & Rows.Count).End(xlUp)).EntireRow.Delete
    [a1].AutoFilter
End Sub

Display More

Gary's Student · Mar 20th 2014

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

Give this a try:

Code

Sub NAKiller()
    Dim r As Range, rDel As Range
    Set rDel = Nothing
    For Each r In Range("A:A").Cells.SpecialCells(xlCellTypeFormulas)
        If r.Text = "#N/A" Then
            If rDel Is Nothing Then
                Set rDel = r
            Else
                Set rDel = Union(rDel, r)
            End If
        End If
    Next r
    If rDel Is Nothing Then Exit Sub
    rDel.EntireRow.Delete
End Sub

Display More

Hoowzer · Mar 20th 2014

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!

Gary's Student · Mar 20th 2014

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

**Trebor76** · Mar 21st 2014

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

This will only delete #N/A errors:

Code

Option Explicit


Sub Macro1()


    'Written by Trebor76
    'Visit my website [NOPARSE]www.excelguru.net.au[/NOPARSE]
    
    'From Row 2, delete any row where Col. A has N/A#.
    
    Dim lngMyCol As Long, _
        lngMyRow As Long
    Dim xlnCalcMethod As XlCalculation
            
    With Application
        xlnCalcMethod = .Calculation
        .Calculation = xlCalculationManual
        .ScreenUpdating = False
    End With


    lngMyCol = Cells.Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column + 1
    lngMyRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    
    With Columns(lngMyCol)
        With Range(Cells(2, lngMyCol), Cells(lngMyRow, lngMyCol)) 'Starts from Row 2. Change to suit.
            .Formula = "=IF(ISERROR(A2)=FALSE,"""",IF(ERROR.TYPE(A2)=7,""DEL"",""""))"
            ActiveSheet.Calculate
            .Value = .Value
        End With
        On Error Resume Next 'Turn error reporting off - OK to ignore 'No cells found' message
            .SpecialCells(xlCellTypeConstants).EntireRow.Delete
        On Error GoTo 0 'Turn error reporting back on
        .Delete
    End With
    
    With Application
        .Calculation = xlnCalcMethod
        .ScreenUpdating = True
    End With
    
MsgBox "All applicable rows have now been deleted."


End Sub

Display More

Regards,

Robert

Hoowzer · Mar 21st 2014

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

Thanks so much - this helps tremendously!

**Trebor76** · Mar 21st 2014

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

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

Participate now!

Share