vba 如何在不跳过行的情况下删除行？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6683107/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Removing rows without skipping a row how to?
提问by Brian
Below is my code. I am trying to move through some rows, and if there is certain data there, then remove those rows, however the current way I have coded it, whenever I delete a row, I skip the one below it. I changed the range to go high numbered rows to low number rows, but my macro still starts at the top and moves down. I thought if I had it move up, a deletion would not cause it to skip the next item. How can I make it move from the bottom of the list upwards, or what is a better way to do this? My code is below:
下面是我的代码。我正在尝试移动一些行，如果那里有某些数据，则删除这些行，但是按照我目前的编码方式，每当我删除一行时，我都会跳过它下面的行。我将范围更改为高编号行到低编号行，但我的宏仍然从顶部开始向下移动。我想如果我让它向上移动，删除不会导致它跳过下一个项目。我怎样才能让它从列表的底部向上移动，或者有什么更好的方法来做到这一点？我的代码如下：
Dim lLastRow As Long
Dim num As Integer
Dim name As String
Dim rCell As Range
Dim afCell As Range
Dim rRng As Range
Dim affectedRng As Range
Windows("Most Affected Customer Info.xls").Activate
Worksheets("Sheet 1").Activate
Cells(1, 1).Select
Selection.End(xlDown).Select
lLastRow = ActiveCell.Row
Set affectedRng = Range("A" & lLastRow & ":A2")
'First remove resolved entries
For Each afCell In affectedRng
If (afCell.Offset(0, 4).Value = "resolved" Or afCell.Offset(0, 4).Value _ = "Resolved" Or afCell.Offset(0, 2).Value = "Resolved" Or afCell.Offset(0, 2).Value = _ "resolved") Then
afCell.EntireRow.Delete
End If
Next afCell
回答by rajah9
How about going through the list backwards?
倒退列表怎么样？
EDIT Some code to try
编辑一些代码来尝试
For row = lLastRow To 1 Step -1
If Range("D" & row).Value = "resolved" Then Rows(row).EntireRow.Delete
Next row
I tested this on a small case with "resolved" in column D and it worked like a champ. You might find that the code both does the trick and reads well.
我在 D 列中“已解决”的一个小案例上对此进行了测试，它的效果非常好。您可能会发现代码既能奏效，又能很好地阅读。
回答by Jon Egerton
You need a do loop and to use the row number as you need to manipulate both your current position in the loop and also the end point;
您需要一个 do 循环并使用行号，因为您需要操作循环中的当前位置和终点；
e.g.
例如
Dim lRow as Long
lRow = 1
Do Until lRow > lLastRow
Set afCell = Cells(lRow ,1)
If (afCell.Offset(0, 4).Value = "resolved" Or afCell.Offset(0, 4).Value _ = "Resolved" Or afCell.Offset(0, 2).Value = "Resolved" Or afCell.Offset(0, 2).Value = _ "resolved") Then
afCell.EntireRow.Delete
'Decrement the last row as we've removed a row
lLastRow = lLastRow - 1
else
'Increment the row number to move to the next one
lRow = lRow + 1
End IF
Loop
Note: this is completely untested so you'll need to debug it, but you should get the gist.
注意：这完全未经测试，因此您需要对其进行调试，但您应该了解要点。
回答by Will
You could replace your For Each with a do while, like this:
你可以用 do while 替换你的 For Each，像这样：
rowx = 2
Do While rowx < llastrow
If Range("B" & rowx).Value = "resolved" Then 'replace this with the columns you're checking
Rows(rowx).EntireRow.Delete
Else
rowx = rowx + 1
End If
Loop
回答by THEn
You could try to change the If condition with While loop.
您可以尝试使用 While 循环更改 If 条件。
For Each afCell In affectedRng
r = afCell.Row
c = afCell.Column
While (afCell.Offset(0, 4).Value = "resolved" Or afCell.Offset(0, 4).Value _ = "Resolved" Or afCell.Offset(0, 2).Value = "Resolved" Or afCell.Offset(0, 2).Value = _ "resolved")
afCell.EntireRow.Delete
Set afCell = Cells(r, c)
Wend
Next afCell
