Will not delete rows

Good Afternoon Experts,

I've got a section that is just not performing as designed / required.

The section of my excel code is Sub CleanUPStage1()

' This section should examine certain fields in the Labor Totals
' and the EQ Totals worksheets for particular values. Then delete
' rows that meet the criteria. In the case of "Labor Totals" these rows
' are Field 2 for either a zero sum or a null value and column 8
' for either a zero sum or a null value.
'
' In the case of "EQ Totals" these rows are Field 3 for either a zero
' sum or a null value and Field 12 for a zero sum or the value #N/A.

Sub CleanUPStage1()

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

' Notification that the process has been completed is displayed
' both on the page and also with a text popup.
MsgBox "Cleanup Stage 1 Complete!"
ThisWorkbook.Worksheets("Controls").Cells(7, 8).Value = "Cleanup Stage 1 Complete"


End Sub
RedstoneITAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

RedstoneITAuthor Commented:
I've tried using other columns to see if the problem was columnular. It doesn't appear to be so.


Attached is a sample spreadsheet I've been working from. It has data populated in the EQ Totals and the Labor totals sheets for your ease of use.


test.xlsm
0
NorieVBA ExpertCommented:
How should it perform?

By the way, I notice you haven't changed the code as recommended in your other question.

That could actually be the problem.

In fact it does seem to be the case.

If you use UsedRange instead of Range("A1") for the filter then the code does delete.
0
RedstoneITAuthor Commented:
I tried changing the code and it still failed. I'll get some captures and post
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

NorieVBA ExpertCommented:
It works fine for me.

As does this.

It adds headers for all the columns of data rather than just putting 'a' in A1.

Sub CleanUPStage1()

Dim rData As Range

Application.ScreenUpdating = False

With Sheets("Labor Totals")
    ' Add headers
    .Range("A1").Resize(, 8).Formula = "=""Field"" & column()"
    .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
    .Rows(1).Clear ' clear header row

End With

Set rData = Nothing

With Sheets("EQ Totals")
     ' Add headers 
     .Range("A1").Resize(, 12).Formula = "=""Field"" & Column()"
    .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
    .Row(1).Clear  ' clear header row
End With

Application.ScreenUpdating = True

    MsgBox "Cleanup Stage 1 Complete!"
    
ThisWorkbook.Worksheets("Controls").Cells(7, 8).Value = "Cleanup Stage 1 Complete"


End Sub

Open in new window

0
RedstoneITAuthor Commented:
okay, that script worked except for the removal of the #N/A rows.

Any suggestions ?

0
NorieVBA ExpertCommented:
The code you posted filters for 0, not #N/A.

Which column(s) do you want to filter for that?

In the previous question, if I recall correctly, it was column 12 on the EQ Totals sheet.

If #N/A is actually an error I don't think you can filter directly for it but you can try this code which adds a 'helper' column.
Sub CleanUPStage1()

Dim rData As Range
Dim NoCols As Long
Dim LastRow As Long

Application.ScreenUpdating = False

With Sheets("Labor Totals")

    NoCols = .Cells(2, Columns.Count).End(xlToLeft)
    
    .Range("A1").Resize(, NoCols).Formula = "=""Field"" & column()"
    .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").Resize(, NoCols + 1).Clear
End With

Set rData = Nothing

With Sheets("EQ Totals")

     NoCols = .Cells(2, Columns.Count).End(xlToLeft).Column
    
     LastRow = .Range("A" & Rows.Count).End(xlUp).Row
        
    .Cells(2, NoCols + 1).Resize(LastRow - 1).Formula = "=ISNA(RC[-1])"
    
     .Range("A1").Resize(, NoCols + 1).Formula = "=""Field"" & Column()"
    .AutoFilterMode = False
    .Range("A1").AutoFilter Field:=3, Criteria1:="0", Operator:=xlOr, Criteria2:=""
    .Range("A1").AutoFilter Field:=13, Criteria1:=True, 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").Resize(, NoCols + 1).Clear
    .Columns(NoCols + 1).Clear
    
End With

Application.ScreenUpdating = True

    MsgBox "Cleanup Stage 1 Complete!"
    
ThisWorkbook.Worksheets("Controls").Cells(7, 8).Value = "Cleanup Stage 1 Complete"


End Sub

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
RedstoneITAuthor Commented:
#N/A is not an error. IS there a way to turn the cell blank if it cant delete the row ?
0
NorieVBA ExpertCommented:
Well whatever it is the code I posted works.
0
NorieVBA ExpertCommented:
Actually hit post too quickly.

If you try  =ISERROR(L6)  where L6 is any of the cells with #N/A it returns TRUE.

If you try =ISTEXT(L6) it returns FALSE.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

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.