VBA：删除具有特定值的行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10077084/
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
VBA: Deleting Rows with a Specific Value
提问by Burton Guster
I'm trying to write a macro to delete all rows that have "True" in column A.
我正在尝试编写一个宏来删除 A 列中具有“True”的所有行。
Here is what I have so far:
这是我到目前为止所拥有的：
Sub deleteBlankRows3()
Dim lastrow as Long
Dim x as Long
lastrow = 4650
For x=8 to x=lastrow
If (Range("A1").Offset(x,0) = True) Then
Range("A1").Offset(x,0).EntireRow.Delete
x = x + 1
End if
Next x
End Sub
I can't tell what's wrong!
我说不出怎么回事！
采纳答案by Alain
Three things might be at work here.
这里可能有三件事在起作用。
First, you should be looking at the cell's value explicitly if you're testing for equivalence for the underlying value:
首先，如果您正在测试基础值的等效性，您应该明确查看单元格的值：
If (Range("A1").Offset(x,0).Value = True) Then
Without saying.Value, I think the cell by default returns it's Text property for equivalence tests against a non-range property.
不用说.Value，我认为默认情况下单元格返回它的 Text 属性，用于针对非范围属性的等效性测试。
Second, your cells probably contain a string "True", rather than the value Trueso try using
其次，您的单元格可能包含字符串“True”，而不是值，True因此请尝试使用
If (Range("A1").Offset(x,0).Value = "True") Then
Finally, if you actually find a row, and you delete it, then your will actually end up skipping a row, because all of the rows after the row being deleted will shift down (row 5 becomes row 4, etc), but you alsojust incremented x, so you will be skipping the row right after every row you deleted. To fix this, either loop in decreasing order:
最后，如果你真的找到了一行，然后删除它，那么你实际上最终会跳过一行，因为被删除的行之后的所有行都会向下移动（第 5 行变成第 4 行，等等），但你也只是增加了 x，因此您将在删除的每一行之后立即跳过该行。要解决此问题，请按降序循环：
For x=lastrow to 8 step -1
or don't increment x if you've just deleted a row:
或者如果您刚刚删除了一行，则不要增加 x：
If (Range("A1").Offset(x,0).Value = "True") Then
Range("A1").Offset(x,0).EntireRow.Delete
Else
x = x + 1
EndIf
回答by Siddharth Rout
I know you have already got what you were looking for. However, still here is another method using Autofilter. This is much faster than looping through each row and checking for the value.
我知道你已经得到了你想要的东西。但是，这里仍然是另一种使用Autofilter. 这比循环遍历每一行并检查值要快得多。
Sub Sample()
Dim lastRow As Long
With Sheets("Sheet1")
lastRow = .Range("A" & Rows.Count).End(xlUp).Row
'~~> Remove any filters
.AutoFilterMode = False
'~~> Filter, offset(to exclude headers) and delete visible rows
With .Range("A1:A" & lastRow)
.AutoFilter Field:=1, Criteria1:="TRUE"
.Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
End With
'~~> Remove any filters
.AutoFilterMode = False
End With
End Sub
HTH
HTH
回答by Eddy
Without testing you are better off like this:
如果没有测试，你最好这样：
For x=lastrow to 8 step -1
If (Range("A1").Offset(x,0) = True) Then
Range("A1").Offset(x,0).EntireRow.Delete
End if
Next
Counting up has an issue that if you delete one row all rows after it move up as well causing your loop not to look at all rows. And since you add 1 to the x in those cases you made it even worse. -1 would have been better except that then you still check 4650+number_of_deleted_rows in total which might lead to other problems. By starting at the end and move towards the start you prevent both those issues.
向上计数有一个问题，如果您在向上移动后删除一行所有行，也会导致您的循环不查看所有行。因为在这些情况下你给 x 加了 1，所以情况更糟。-1 会更好，除非您仍然总共检查 4650+number_of_deleted_rows，这可能会导致其他问题。通过从最后开始并朝着开始前进，您可以防止这两个问题。
回答by ervinbosenbacher
The problem is that the algorithm is incorrect. Classic case for the corrupted loop variable. The problem is that the variable that the loop is dependent on gets modified, as such it is wrong.
问题是算法不正确。损坏的循环变量的经典案例。问题是循环所依赖的变量被修改，因此它是错误的。
The correct way to do it is this way.
正确的做法是这样。
Dim x as integer
x = 8
do
if (Range("a1").Offset(x, 0) = True) Then
Range("a1").Offset(x, 0).EntireRow.Delete
Else
x = x + 1 'We only increase the row number in the loop when we encounter a row that is false for containing true in cell a1 and their offsets
End If
Loop Until (x > 4650)
回答by sirplus
I had hidden lines and didn't want to unhide them which the filtering method does. Also didn't want to loop through every line so here's my 10c.....
我有隐藏的线条，不想像过滤方法那样取消隐藏它们。也不想遍历每一行，所以这是我的 10c .....
Sub DelError()
Dim i As Integer
Dim rngErrRange As Range
With ActiveSheet
Do
Set rngErrRange = .Columns("A:A").Find(What:="#REF!", _
After:=.Cells(1), LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
If Not rngErrRange Is Nothing Then
rngErrRange.EntireRow.Delete
Else
End
End If
Loop
End With
End Sub
