VBA删除行无法正常工作 [英] vba delete row not working properly

问题描述

我一直在尝试使用某些VBA修改工作表中的数据，不幸的是，我正在使用的以下代码无法正常工作. 基本上，列A具有文本，并且如果列A是坑"(不包含坑"，而仅包含坑")，我想删除整行.由于某种原因，代码仅删除了某些行，而没有删除其他行，因此我不得不继续运行脚本几次以摆脱所有的坑".删除的行与未删除的行之间没有明显区别，它们都是文本&没空间了.成千上万的行具有不同的A列文本.这是代码，我将不胜感激任何建议.

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

谢谢！

推荐答案

删除行时只需向后循环:

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

原因是，当删除一行并将i加一时，您基本上跳过了下一行，因为删除将那一行向上移动了.

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.

另一种方法是在EntireRow.Delete行之后添加i = i - 1.

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

这篇关于VBA删除行无法正常工作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！