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