asked on

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

RedstoneIT

ASKER

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

Norie

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.

RedstoneIT

ASKER

I tried changing the code and it still failed. I'll get some captures and post

Norie

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

RedstoneIT

ASKER

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

Any suggestions ?

ASKER CERTIFIED SOLUTION

Norie

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

RedstoneIT

ASKER

#N/A is not an error. IS there a way to turn the cell blank if it cant delete the row ?

Norie

Well whatever it is the code I posted works.

Norie

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.