VBA - Delete entire row if criteria is not met

[Lalo2DaG]

Can someone please help me with VBA code. Here is my real world scenario. I have a report that contains several different statuses. I only need 4 of the many statuses. Ideally, the VBA code would delete rows that does not contain the 4 statuses. In addition, I would also like the code to not delete the first 7 rows at all. The first 7 rows contain miscellaneous information including the headers. Any help with this would be greatly appreciated.

FYI - I was on another thread and it was suggested that I may try my own thread to meet my needs. Here is the link to the thread I was on.

http://www.mrexcel.com/forum/excel-...applications-delete-row-if-cell-contains.html

 

[svendiamond]

I have to manipulate reports like this all the time and my favorite way is to have a helper column that says basically "=IF(A2=valueToDelete,NA(),A2)" where valueToDelete in your case would be a status you dont' want. You could probably think of some IF/NOT/OR function to return NA() for the ones you don't want. Anyway, after that helper column has errors in place of the values you don't want, you can use it to delete the rows like this:

Dim r As RangeOn Error Resume Next
Set r = Sheets("mySheet").Range("D:D").Cells.SpecialCells(xlCellTypeFormulas, xlErrors)
On Error GoTo 0
If Not r Is Nothing Then
  r.EntireRow.Delete
End If

Which deletes rows where the value in column D is #N/A. If this isn't helpful, here is a link that may provide you with more information: How to remove rows based on cell value in Excel?

 

[Trebor76]

Hi Lalo2DaG,

I agree with svendiamond about using a helper column (I have answered numerous threads about deleting rows using this method). It is an especially good method for large datasets as it doesn't involve looping.

What are the four statuses you want to keep and what column are they in?

Robert

 

[Lalo2DaG]

Thanks svendiamond and Trebor76. I haven't used a helper column before, but it does sound ideal for what I'm trying to do. The four statuses I want to keep are "MAIL","SIGN","EDIT", and "SCHD". These status are in the I column.

I should add, that there are over 50 statuses and I only want to keep 4 of them. I guess I'm not sure how I would do the helper column to do this.

 

[svendiamond]

I should add, that there are over 50 statuses and I only want to keep 4 of them. I guess I'm not sure how I would do the helper column to do this.

=IF(OR(A1={"MAIL","SIGN","EDIT","SCHD"}),A1,NA())

Basically what your helper column is doing is returning an error if it's not one of those four. Then you could use the code I posted earlier to delete the rows that have an error in that column.

 

[Trebor76]

Try this (though initially on a copy of your data as the results cannot be undone if they're not as expected) while on the sheet in question:

Option Explicit
Sub Macro1()

    Const lngStartRow As Long = 2 'Starting data row number. Change to suit.
    
    Dim lngMyCol As Long, _
        lngMyRow As Long
    Dim xlnCalcMethod As XlCalculation
            
    With Application
        xlnCalcMethod = .Calculation
        .Calculation = xlCalculationManual
        .ScreenUpdating = False
    End With

    lngMyCol = Cells.Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column + 1
    lngMyRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    
    With Columns(lngMyCol)
        With Range(Cells(lngStartRow, lngMyCol), Cells(lngMyRow, lngMyCol))
            .Formula = "=VLOOKUP(I2,{""MAIL"";""SIGN"";""EDIT"";""SCHD""},1,FALSE)"
            ActiveSheet.Calculate
            .Value = .Value
        End With
        On Error Resume Next 'Turn error reporting off - OK to ignore 'No cells found' message
            .SpecialCells(xlCellTypeConstants, xlErrors).EntireRow.Delete
        On Error GoTo 0 'Turn error reporting back on
        .Delete
    End With
    
    With Application
        .Calculation = xlnCalcMethod
        .ScreenUpdating = True
    End With

    MsgBox "All rows from Col. I where the status was not one of the four desired statuses have now been deleted.", vbInformation

End Sub

Regards,

Robert

 

[Lalo2DaG]

Thank you! This works great. At first I was having problems with it because it wouldn't work the way I wanted it to, but I then realized I had a column that contained the initials of the employee assigned and if there was no employee assigned it would contain "***". I think the three *'s where throwing the code off, so I deleted the column and it worked like magic. Thanks for the help.

 

[Trebor76]

I think the three *'s where throwing the code off, so I deleted the column and it worked like magic.

No, that shouldn't have made a difference. As long as the statuses were in column I (not even case sensitive) all should be good

As long as it's working that's all that matters I suppose.

Regards,

Robert

 

[My Aswer Is This]

Try this:

Sub Delete_Me()
Application.ScreenUpdating = False
Dim i As Integer
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "I").End(xlUp).Row

    For i = Lastrow To 8 Step -1
        If Cells(i, 9).Value <> "MAIL" And Cells(i, 9).Value <> "SIGN" And Cells(i, 9).Value <> "EDIT" And Cells(i, 9).Value <> "SCHD" Then
            Rows(i).Delete
            End If
    Next
Application.ScreenUpdating = True

End Sub

 

[Lalo2DaG]

It was all me. I was just being an idiot. It works well. Thanks!!!!!