0

I am trying to trim down a data set by deleting rows that have certain criteria. For example I want to delete any rows that have "1WIP" in the Sub inventory column.

When the code runs, not all of the instances are deleted. In the example above, instances were cut from 9280 to 270.

What can I do to make sure that all of the instances where the criteria are met are deleted?

Sub BucketList()


'Clear Sheet1
Sheets("Sheet1").Select
Sheets("Sheet1").Cells.Select
    Selection.ClearContents
    Sheets("BPL").Select
    Range("A1").Select

'Make a table
Dim tbl As Range
Dim ws As Worksheet

Set tbl = ThisWorkbook.Worksheets("BPL").Range("Q1").CurrentRegion
Set ws = ThisWorkbook.Worksheets("BPL")

ws.ListObjects.Add(SourceType:=xlSrcRange, Source:=tbl).Name = "List"


''''''''''''''''''SUBINVENTORY'''''''''''''''''''''''''''''''
'1WIP
  For i = 2 To ThisWorkbook.Worksheets("BPL").UsedRange.Rows.Count
    If Cells(i, 7).Value = "1WIP" Then
        Rows(i).EntireRow.Delete
    End If
    If Cells(i, 7).Value = "1WIP" Then
        Rows(i).EntireRow.Delete
    End If
Next i
Bert328
  • 119
  • 8
  • 2
    When looping thru and deleting rows, you need to step backwards instead so you dont run into problems like this. – braX Mar 23 '22 at 15:46
  • 1
    Even better, don't delete inside the loop, instead use `Union` to build up the row(s) to delete, then delete *after* looping (demonstrated in the linked thread, and in many other questions on SO). – BigBen Mar 23 '22 at 15:48
  • 1
    Or use `Range.AutoFilter` to filter for those rows, and then delete. – BigBen Mar 23 '22 at 15:51

0 Answers0