CODEWITHSUNDEEP
excelvbafor-loop
JSL
JSL

Reputation: 13

Loop not deleting all the required rows

My code is supposed to count the number of rows that are in the WIP worksheet and then loop through the rows and delete a row if its string in column B contains the word "out".

Sub delete()
    numrows = Worksheets("WIP").Cells(Rows.Count, "A").End(xlUp).Row

    For x = 1 To numrows
        If InStr(1, lCase(Range("B" & x)), "out") <> 0 Then
            Rows(x).Delete
        End If
    Next
End Sub

The code is running without getting any errors but it only deletes some of the lines. Say there are 100 rows containing the word "out" in column B. I will run the code once and it will get rid of a few rows. Then I run it again and it gets rid of a few more. If I run the code enough times it will get rid of all the right rows.

I'm not sure why all the rows aren't deleting the first time around, any help would be much appreciated. Thanks.

Upvotes: 1

Views: 201

Answers (4)

user8608712
user8608712

Reputation:

Have you tried using .FindNext? I think this maybe a little faster and avoid your problems:

With sheets(“WIP”).Range(“B1:B” & x)

Set mycell = .Cells.Find(What:=“out”)

    If Not mycell Is Nothing Then

        Do Until mycell Is Nothing

            Cells(mycell.Row, “B”).entireRow.Delete

            Set mycell = .FindNext(mycell)
        Loop

End If

End with

Upvotes: 1

Daniel
Daniel

Reputation: 954

Another way, which I believe is most efficient when dealing with large amount of data, is to .ClearContents rather than .EntireRow.Delete, and at the end delete all blank rows in one go. Or even better, consider this:

Columns("B:B").Replace What:="out", Replacement:=vbNullString, LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Columns("B:B").SpecialCells(xlCellTypeBlanks).EntireRow.delete

Note it will delete rows if B is blank so adjust to your data, but this is the quickest way to do what you're trying to do.

Upvotes: 2

user11217663
user11217663

Reputation:

While the best response is to loop backwards (thereby avoiding skipping over rows that have been shifted up when deleted), you could also nest in a Do While loop.

Option Explicit

Sub delete()

  dim x as long, numrows as long

  with Worksheets("WIP")

    numrows = .Cells(Rows.Count, "A").End(xlUp).Row

    For x = 1 To numrows

      Do While InStr(1, Range("B" & x), "out", vbTextCompare) > 0
        Rows(x).Delete
      Loop

    Next

  End With

End Sub

Upvotes: 0

Gary&#39;s Student
Gary&#39;s Student

Reputation: 96771

Replace:

For x = 1 To numrows

with:

For x = numrows to 1 Step -1

The loop index gets corrupted if you run it forwards.

Upvotes: 7

Related Questions