Delete entire row IF cell contains one of values from range

[Akbarov]

Hello dear community,

I use following VBA to delete entire row if cell contains "6" or "7"

But now I want to add a lot more criterias. Is it possible to delete entire row if cells A40:A3000 contains any value from range B1:B40 ?

    Dim rng As Range
    Set rng = ActiveSheet.Range("A40:A3000")
    
    For i = rng.Cells.Count To 1 Step -1
        If rng.Item(i).Value = "6" Or rng.Item(i).Value = "7" Then
            rng.Item(i).EntireRow.Delete
        End If
    Next i

 

[kevin9999]

Try this on a copy of your data.

Option Explicit
Sub Akbarov()
    Dim ws As Worksheet, Arr, i As Long
    Set ws = ActiveSheet
    Arr = ws.Range("B1:B40").Value
    Arr = Application.Transpose(Application.Index(Arr, 0, 1))
    
    For i = LBound(Arr) To UBound(Arr)
        Arr(i) = CStr(Arr(i))
    Next i
    
    With ws.Range("A40:A3000")
        .AutoFilter 1, Array(Arr), 7
        .Offset(1).Resize(.Rows.Count - 1).EntireRow.Delete
        .AutoFilter
    End With

End Sub

 

[My Aswer Is This]

Try this:

Sub Delete_Me_With_Case()
'Modified  10/6/2022  7:28:09 PM  EDT
Application.ScreenUpdating = False
Dim i As Long
    
    For i = 3000 To 40 Step -1
        Select Case Cells(i, 1).Value
            Case Range("B1") To Range("B40")
                Rows(i).Delete
        End Select
    Next
   Application.ScreenUpdating = True

   MsgBox "Done"
End Sub

 

[Akbarov]

Thank you both, works!

 

[kevin9999]

Thank you both, works!

Glad we could help, and thanks for the feedback

 

[My Aswer Is This]

Glad we were able to help you.
Come back here to Mr. Excel next time you need additional assistance.