vba delete row not working properly

Question

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!

Possible duplicate of [Excel VBA deleting rows in a for loop misses rows](https://stackoverflow.com/questions/43454139/excel-vba-deleting-rows-in-a-for-loop-misses-rows) — Egan Wolf, Jul 10 '17 at 08:11

score 1 · Accepted Answer · answered Jul 10 '17 at 08:06

1

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.

answered Jul 10 '17 at 08:06

Rik Sportel

2,661
1
14
24

Cool, thanks for including the step -1, I didn't know how to do that! – Baby face Jul 10 '17 at 08:12
I think i would suggest to focus on step -1, adding it did help me run my code. – pythonic_love Dec 14 '22 at 17:39

score 0 · Answer 2 · answered Jul 10 '17 at 08:02

0

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
...

answered Jul 10 '17 at 08:02

zipa

27,316
6
40
58

ahh I see. In that case would it work if I directly manipulate i? ie. i=i-1 rather than including c? – Baby face Jul 10 '17 at 08:07
Technically you can, but this seems cleaner to me :) – zipa Jul 10 '17 at 10:16

ainwood · Answer 3 · 2017-07-10T08:12:20.167

0

@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.

edited Jul 10 '17 at 08:12

answered Jul 10 '17 at 08:06

ainwood

992
7
17

1

You need to add `Step -1` in loop declaration. – Egan Wolf Jul 10 '17 at 08:09
ahh, very clever! – Baby face Jul 10 '17 at 08:12
Ah - thanks @EganWolf. You are quite right. Fixed. – ainwood Jul 10 '17 at 08:12

vba delete row not working properly

3 Answers3

Linked