Delete Rows Based on criteria

Good Afternoon,

The code below was working fine, but for some reason I am getting the below error now and are not sure what the deal is.

Run-Time Error '1004':
Cannot use that command for overlapping selections.

Goal: I am trying to search through Columns A,B,C,D and delete rows that contain the following items. Below is the code I have been using.

#N/A
#VALUE!
Blank Values
Rows that contain a cell with a single space
Rows that contain a Cell that contain the value 0 (0 by itself)
Rows that contain a Cell that contain $0.00

Sub DeleteInvalidData()
  Dim rngFound As Range, rngToDelete As Range
    Dim strFirstAddress As String
    Dim varList As Variant
    Dim lngCounter As Long
    
    varList = VBA.Array("#N/A", "#VALUE!", " ", "", "0", "$0.00")
    
    For lngCounter = LBound(varList) To UBound(varList)
    
        With Sheets("ChargeMaster_Template").Range("A:C")
            Set rngFound = .Find( _
                                What:=varList(lngCounter), _
                                LookAt:=xlWhole, _
                                SearchOrder:=xlByRows, _
                                SearchDirection:=xlNext, _
                                MatchCase:=True _
                                    )

            
            If Not rngFound Is Nothing Then
                If rngToDelete Is Nothing Then
                    Set rngToDelete = rngFound
                Else
                    Set rngToDelete = Application.Union(rngToDelete, rngFound)
                End If
                
                strFirstAddress = rngFound.Address
                Set rngFound = .FindNext(after:=rngFound)
                
                Do Until rngFound.Address = strFirstAddress
                    Set rngToDelete = Application.Union(rngToDelete, rngFound)
                    Set rngFound = .FindNext(after:=rngFound)
                Loop
            End If
        End With
    Next lngCounter
    
    If Not rngToDelete Is Nothing Then rngToDelete.EntireRow.Delete
End Sub

As always, any help is greatly appreciated.

You have 2 options:

 If Not rngToDelete Is Nothing Then rngToDelete.Delete

Or
You need to put the entire range in the union so as to use the entrierow delete

try

Sub DeleteInvalidData()
    Dim i As Long
    With Cells(1).CurrentRegion
        .Parent.AutoFilterMode = False
        For i = 1 To .Columns.Count
            .AutoFilter i, VBA.Array("#N/A", "#VALUE!", " ", "", "0", "$0.00"), xlFilterValues
            .Offset(1).EntireRow.Delete
            .AutoFilter
        Next
    End With
End Sub

Thanks Jindon - This worked perfectly. Really have been appreciating your help.

AB33 - Thank you as well.

You are welcome and thanks for the rep.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

matrix2280,

You can not ask any question via PM by the rule here.

Originally Posted by matrix2280

In this instance I need to run this code for Columns A,B (skip C) and then run the code again for Column D.

Chane to

Sub DeleteInvalidData()
    Dim i As Long
    With Cells(1).CurrentRegion
        .Parent.AutoFilterMode = False
        For i = 1 To .Columns.Count
            If i <> 3 Then
                .AutoFilter i, VBA.Array("#N/A", "#VALUE!", " ", "", "0", "$0.00"), xlFilterValues
                .Offset(1).EntireRow.Delete
                .AutoFilter
            End If
        Next
    End With
End Sub

Thanks again Jindon

Jindon,

For some reason (using the code above), I added a new value "#NAME?" and it will not remove it. Any ideas?

Try this one then

Sub DeleteInvalidData()
    Dim i As Long
    With Cells(1).CurrentRegion
        .Parent.AutoFilterMode = False
        For i = 1 To .Columns.Count
            If i <> 3 Then
                On Error Resume Next
                .SpecialCells(-4123, 16).EntireRow.Delete
                On Error GoTo 0
                .AutoFilter i, VBA.Array("#N/A", "#VALUE!", " ", "", "0", "$0.00"), xlFilterValues
                .Offset(1).EntireRow.Delete
                .AutoFilter
            End If
        Next
    End With
End Sub

Jindon - It didn't remove it. I will add that when the code runs all of the data is set as text.

What is wierd is I can search for #NAME? and replace it with another value, and if i do the filter manually it finds it, but won't remove.

Can you upload a file?