Deleting a Row with a specific data shown.

[jffryjsphbyn]

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.

 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.

[princ_wns]

Hi You Can refer this

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