CODEWITHSUNDEEP
excelvba
Baby face
Baby face

Reputation: 15

vba delete row not working properly

I have been trying to modify the data in a work sheet with some VBA, unfortunately the following code I'm using is not working properly. Basically column A has the text, and I want to delete the entire row if the column A is "Pit" (not containing "Pit", but only "Pit"). For some reason the code is only deleting some rows but not others, so I have to keep running the script a few times to get rid of all the "Pit"s. There is nothing distinctly different between the rows it deletes and the ones it does not, they are all text & no spaces. There are thousands of rows with different column A text. Here is the code, I would greatly appreciate any suggestions.

Sub Pitdelete()

Dim lastrow As Long
Dim datasheet As Worksheet
Dim i As Long

Set datasheet = Worksheets("DefCatCou")

lastrow = datasheet.Range("a" & datasheet.Rows.Count).End(xlUp).Row

For i = 2 To lastrow
If datasheet.Cells(i, 1) = "Pit" Then
datasheet.Rows(i & ":" & i).EntireRow.delete
End If

Next i

End Sub

Thanks!

Upvotes: 0

Views: 9426

Answers (3)

ainwood
ainwood

Reputation: 1048

@zipa is quite right - when you delete a row, the others move up, changing their index. As an alternative to his proposal, you can get the loop to run in reverse:

For i = lastrow To 2 Step -1
    If datasheet.Cells(i, 1) = "Pit" Then
        datasheet.Rows(i & ":" & i).EntireRow.delete
    End If
next i

That way, if you delete a row, it won't affect the next index in your loop.

Upvotes: 0

Rik Sportel
Rik Sportel

Reputation: 2679

Just loop backwards when deleting rows:

Sub Pitdelete()

Dim lastrow As Long
Dim datasheet As Worksheet
Dim i As Long

Set datasheet = Worksheets("DefCatCou")

lastrow = datasheet.Range("a" & datasheet.Rows.Count).End(xlUp).Row

For i = lastrow To 2 step -1 'This should fix it.
    If datasheet.Cells(i, 1) = "Pit" Then
        datasheet.Rows(i & ":" & i).EntireRow.delete
    End If
Next i
End Sub

Reason is that when you delete a row and increase the i with one, you basically skip the next row, since the delete shifted that one up.

The alternative is to add i = i - 1 after the EntireRow.Delete line.

Upvotes: 1

zipa
zipa

Reputation: 27869

Each time you delete a row all the rows below it move up.

You can declare another variable like this and increase it each time you delete row:

Dim c as Integer
c = 0
If datasheet.Cells(i, 1) = "Pit" Then
datasheet.Rows(i - c & ":" & i - c).EntireRow.delete
c = c + 1
...

Upvotes: 0

Related Questions