[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Will not delete rows

Posted on 2011-10-15
9
Medium Priority
?
326 Views
Last Modified: 2012-05-12
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
0
Comment
Question by:RedstoneIT
  • 5
  • 4
9 Comments
 

Author Comment

by:RedstoneIT
ID: 36973754
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
 
LVL 35

Expert Comment

by:Norie
ID: 36973782
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
 

Author Comment

by:RedstoneIT
ID: 36973809
I tried changing the code and it still failed. I'll get some captures and post
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 35

Expert Comment

by:Norie
ID: 36973819
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
 

Author Comment

by:RedstoneIT
ID: 36974015
okay, that script worked except for the removal of the #N/A rows.

Any suggestions ?

0
 
LVL 35

Accepted Solution

by:
Norie earned 2000 total points
ID: 36974244
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
 

Author Comment

by:RedstoneIT
ID: 36975852
#N/A is not an error. IS there a way to turn the cell blank if it cant delete the row ?
0
 
LVL 35

Expert Comment

by:Norie
ID: 36976035
Well whatever it is the code I posted works.
0
 
LVL 35

Expert Comment

by:Norie
ID: 36976044
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

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question