Issue with deleting cells

I've got code that is supposed to delete any rows that contain null values or a 0 in rows B and D in one worksheet and Row C in another worksheet. It is clearing out most of the rows, but I still have rows that have Zeros in them being left behind. I've attached a copy of the workbook I'm working on.

The sub routine in question is CleanUP()

You can see the results by looking at Labor Totals  

I need those rows with Zeros to be gone but I cant figure out why its failing to remove them.


Sub CleanUP()

Dim rData As Range

Application.ScreenUpdating = False

With Sheets("Labor Totals")
    .Range("A1").Value = "a"
    .AutoFilterMode = False
    .Range("A1").AutoFilter Field:=2, Criteria1:="0", Operator:=xlOr, Criteria2:=""
    .Range("A1").AutoFilter Field:=4, Criteria1:="0", Operator:=xlOr, Criteria2:=""
    With .AutoFilter.Range
        On Error Resume Next
        Set rData = .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible)
        On Error GoTo 0
        If Not rData Is Nothing Then
            rData.EntireRow.Delete
        End If
    End With
    .AutoFilterMode = False
    .Range("A1").Clear
End With

Set rData = Nothing

With Sheets("EQ Totals")
    .Range("A1").Value = "a"
    .AutoFilterMode = False
    .Range("A1").AutoFilter Field:=3, Criteria1:="0", Operator:=xlOr, Criteria2:=""
    With .AutoFilter.Range
        On Error Resume Next
        Set rData = .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible)
        On Error GoTo 0
        If Not rData Is Nothing Then
            rData.EntireRow.Delete
        End If
    End With
    .AutoFilterMode = False
    .Range("A1").Clear
End With

Application.ScreenUpdating = True

End Sub  
Copy-of-working.xlsm
RedstoneITAsked:
Who is Participating?
 
byundtCommented:
If you mean to have an OR criteria on columns B and D, then try doing filters on columns B and D separately:

Sub CleanUP()

Dim rData As Range

Application.ScreenUpdating = False

With Sheets("Labor Totals")
    .Range("A1").Value = "a"
    .AutoFilterMode = False
    .Range("A1").AutoFilter Field:=2, Criteria1:="0", Operator:=xlOr, Criteria2:=""
    With .AutoFilter.Range
        On Error Resume Next
        Set rData = .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible)
        On Error GoTo 0
        If Not rData Is Nothing Then
            rData.EntireRow.Delete
        End If
    End With
    .AutoFilterMode = False
    .Range("A1").AutoFilter Field:=4, Criteria1:="0", Operator:=xlOr, Criteria2:=""
    With .AutoFilter.Range
        On Error Resume Next
        Set rData = .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible)
        On Error GoTo 0
        If Not rData Is Nothing Then
            rData.EntireRow.Delete
        End If
    End With
    .AutoFilterMode = False
    .Range("A1").Clear
End With

Set rData = Nothing

With Sheets("EQ Totals")
    .Range("A1").Value = "a"
    .AutoFilterMode = False
    .Range("A1").AutoFilter Field:=3, Criteria1:="0", Operator:=xlOr, Criteria2:=""
    With .AutoFilter.Range
        On Error Resume Next
        Set rData = .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible)
        On Error GoTo 0
        If Not rData Is Nothing Then
            rData.EntireRow.Delete
        End If
    End With
    .AutoFilterMode = False
    .Range("A1").Clear
End With

Application.ScreenUpdating = True

End Sub

Open in new window

0
 
byundtCommented:
You are filtering with an AND criteria on columns B and D in Labor Rates. Try commenting out the following line of code:
    .Range("A1").AutoFilter Field:=4, Criteria1:="0", Operator:=xlOr, Criteria2:=""
0
 
RedstoneITAuthor Commented:
Any idea why it wont work on field 8 ? Column G ?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.