Delete all rows not containing specific information in column A

[mrmcoupe]

Hi!

I have this code deleting all rows that do not contain the text "A#ABN" in column A. However, I also need to keep rows containing the value "A#UUW" also. Single delete works perfectly, but I struggle when adding more criteria. I´ve tried different versions of code two, but still noting

1) This one works fine:

Sub delete_row()    

Dim r As Long, lr As Long
    lr = Cells(Rows.Count, 1).End(xlUp).Row
    For r = lr To 2 Step -1
        If InStr(Cells(r, 1), "A#ABN") = 0 Then Rows(r).Delete
        Next r
End Sub

2) This does not work:

Sub delete_row()    

Dim r As Long, lr As Long
    lr = Cells(Rows.Count, 1).End(xlUp).Row
    For r = lr To 2 Step -1
        If InStr(Cells(r, 1), "A#ABN" [U][I]Or "A#UUW")[/I][/U] = 0 Then Rows(r).Delete
        Next r
End Sub

 

[Joe4]

Try:

If (InStr(Cells(r, 1), "A#ABN") = 0) Or (InStr(Cells(r, 1), "A#UUW") = 0) Then Rows(r).Delete

 

[mrmcoupe]

Try:

If (InStr(Cells(r, 1), "A#ABN") = 0) Or (InStr(Cells(r, 1), "A#UUW") = 0) Then Rows(r).Delete

Hi Joe4,

thanks for reply!

This one seemed logic for me also, but I´ve tried this one as well, but then all rows are deleted. :/

 

[Joe4]

Can you post a handful of examples of the entries that might appear in the cells (some that will be deleted, and some that will not)?

 

[JackDanIce]

Try:

Sub DelRows()


    Dim x       As Long
    Dim arr()   As Variant
    
    arr = Cells(2, 1).Resize(Cells(Rows.Count, 1).End(xlUp).row).Value
    
    For x = LBound(arr, 1) To UBound(arr, 1)
        If inster(arr(x, 1), "A#BN") + inster(arr(x, 1), "A#UW") = 0 Then arr(x, 1) = Null
    Next x
    
    Application.ScreenUpdating = False
    With Cells(2, 1).Resize(UBound(arr, 1), UBound(arr, 2))
        .Value = arr
        .SpecialCells(xlCellTypeBlanks).EntireRow.Delete
    End With
    Application.ScreenUpdating = True
    
    Erase arr
    
End Sub

 

[Joe4]

I misread your original post. You want to NOT to delete the ones that contain that text (not delete them).

So all you have to do in the last code I posted is change the word OR to AND and it will work.

 

[mrmcoupe]

Yes, perfect! That worked as I hoped. I think I tried a version of this, but I couldnt make it happen. Thank you @Joe4!

BTW: Sorry for late reply, been away for a couple of days!

brg
H