Deleting a Row with a specific data shown.

Hi everyone! Please help me again to delete a specific row that has a value of: "Opened", "Received" in VBA. :)



I know it is on the filtration of data and manually scrolling up to the end and finally delete it manually.

Please help! :)

I have also a record of my previous macro but I wanted to have more criteria to delete it. and it will be under the case statement.

Code:

---

Sheets("SEGREGATION").Select
    Cells.Select
    Selection.AutoFilter
    Range("I3").Select
    ActiveSheet.Range("$A$1:$Q$592").AutoFilter Field:=9, Criteria1:="=Pending" _
        , Operator:=xlOr, Criteria2:="=Received"
    ActiveWindow.ScrollColumn = 5
    ActiveWindow.ScrollColumn = 4
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 1
    Rows("5:1832").Select
    ActiveWindow.SmallScroll Down:=-141
    Selection.Delete Shift:=xlUp
    ActiveWindow.SmallScroll Down:=-72
    ActiveWindow.ScrollRow = 1579
    ActiveWindow.ScrollRow = 1571
    ActiveWindow.ScrollRow = 1567
    ActiveWindow.ScrollRow = 1561
    ActiveWindow.ScrollRow = 1553
    ActiveWindow.ScrollRow = 1541
    ActiveWindow.ScrollRow = 1525
    ActiveWindow.ScrollRow = 1505
    ActiveWindow.ScrollRow = 1488
    ActiveWindow.ScrollRow = 1468
    ActiveWindow.ScrollRow = 1448
    ActiveWindow.ScrollRow = 1428
    ActiveWindow.ScrollRow = 1412
    ActiveWindow.ScrollRow = 1400
    ActiveWindow.ScrollRow = 1386
    ActiveWindow.ScrollRow = 1374
    ActiveWindow.ScrollRow = 1356
    ActiveWindow.ScrollRow = 1338
    ActiveWindow.ScrollRow = 1318
    ActiveWindow.ScrollRow = 1297
    ActiveWindow.ScrollRow = 1271
    ActiveWindow.ScrollRow = 1247
    ActiveWindow.ScrollRow = 1225
    ActiveWindow.ScrollRow = 1203
    ActiveWindow.ScrollRow = 1185
    ActiveWindow.ScrollRow = 1165
    ActiveWindow.ScrollRow = 1145
    ActiveWindow.ScrollRow = 1128
    ActiveWindow.ScrollRow = 1106
    ActiveWindow.ScrollRow = 1084
    ActiveWindow.ScrollRow = 1058
    ActiveWindow.ScrollRow = 1034
    ActiveWindow.ScrollRow = 1006
    ActiveWindow.ScrollRow = 976
    ActiveWindow.ScrollRow = 887
    ActiveWindow.ScrollRow = 710
    ActiveWindow.ScrollRow = 620
    ActiveWindow.ScrollRow = 553
    ActiveSheet.Range("$A$1:$Q$566").AutoFilter Field:=9
    ActiveWindow.SmallScroll Down:=-198
    ActiveWindow.ScrollRow = 366
    ActiveWindow.ScrollRow = 362
    ActiveWindow.ScrollRow = 356
    ActiveWindow.ScrollRow = 343
    ActiveWindow.ScrollRow = 314
    ActiveWindow.ScrollRow = 240
    ActiveWindow.ScrollRow = 150
    ActiveWindow.ScrollRow = 130
    ActiveWindow.ScrollRow = 120
    ActiveWindow.ScrollRow = 116
    ActiveWindow.ScrollRow = 112
    ActiveWindow.ScrollRow = 110
    ActiveWindow.ScrollRow = 109
    ActiveWindow.ScrollRow = 106
    ActiveWindow.ScrollRow = 104
    ActiveWindow.ScrollRow = 102
    ActiveWindow.ScrollRow = 99
    ActiveWindow.ScrollRow = 96
    ActiveWindow.ScrollRow = 93
    ActiveWindow.ScrollRow = 88
    ActiveWindow.ScrollRow = 85
    ActiveWindow.ScrollRow = 83
    ActiveWindow.ScrollRow = 80
    ActiveWindow.ScrollRow = 76
    ActiveWindow.ScrollRow = 74
    ActiveWindow.ScrollRow = 71
    ActiveWindow.ScrollRow = 68
    ActiveWindow.ScrollRow = 64
    ActiveWindow.ScrollRow = 60
    ActiveWindow.ScrollRow = 56
    ActiveWindow.ScrollRow = 52
    ActiveWindow.ScrollRow = 48
    ActiveWindow.ScrollRow = 40
    ActiveWindow.ScrollRow = 32
    ActiveWindow.ScrollRow = 27
    ActiveWindow.ScrollRow = 24
    ActiveWindow.ScrollRow = 15
    ActiveWindow.ScrollRow = 7
    ActiveWindow.ScrollRow = 2
    Range("A2").Select
    Sheets("INSTRUCTIONS").Select
End Sub

---

Thank you in advance. :)

Hi You Can refer this

Code:

---

Public Sub DeleteRow()
   
    Dim strC1      As String
    Dim strC2      As String
    Dim strSht      As String
    Dim rngData    As Range
    Dim lngColumns  As Long
    Dim rngCell    As Range
   
    strSht = "Sheet1"  'Here you can type the sheet name
    strC1 = "Opened"    'Here add the first criteria
    strC2 = "Received"  'Here add the secod Criteria
    lngColumns = 9      'Here add the columns No in which your criteria may exist
                       
    With ThisWorkbook.Worksheets(strSht)
        Set rngData = .Range("A1:Q1000")
        For Each rngCell In rngData.Columns(lngColumns)
            If rngCell.Value = strC1 Or rngCell.Value = strC2 Then
                rngCell.EntireRow.Delete
            End If
        Next
    End With
   
    strC1 = vbNullString
    strC2 = vbNullString
    strSht = vbNullString
    Set rngData = Nothing
    Set rngCell = Nothing
    lngColumns = Empty   
End Sub

---