vba 如何使用VBA根据条件删除Excel中的行？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7648655/
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
How to delete rows in Excel based on criteria using VBA?
提问by Laura Kane-Punyon
I am currently building a macro to format a sheet of data as well as to remove inapplicable rows of data. Specifically, I am looking to delete rows where Column L = "ABC" as well as delete rows where Column AA <> "DEF".
我目前正在构建一个宏来格式化数据表以及删除不适用的数据行。具体来说，我希望删除列 L = "ABC" 的行以及删除列 AA <> "DEF" 的行。
So far I have been able to achieve the first objective, but not the second. The existing code is:
到目前为止，我已经能够实现第一个目标，但没有实现第二个目标。现有代码为：
Dim LastRow As Integer
Dim x, y, z As Integer
Dim StartRow, StopRow As Integer
For x = 0 To LastRow
If (Range("L1").Offset(x, 0) = "ABC") Then
Range("L1").Offset(x, 0).EntireRow.Delete
x = x - 1
End If
回答by brettdj
It is normally much quicker to use AutoFilter rather than loop Ranges
通常使用自动过滤器而不是循环范围要快得多
The code below creates a working column, then use a formula to detect delete criteria and then autofilter and delete the result records
下面的代码创建一个工作列，然后使用公式检测删除条件，然后自动过滤并删除结果记录
The working column puts a formula
工作栏放一个公式
=OR(L1="ABC",AA1<>"DEF")into row 1 of the first blank column then copies down as far ar the true used range. Then any TRUE records are quicklly deleted with AutoFilter
=OR(L1="ABC",AA1<>"DEF")到第一个空白列的第 1 行，然后向下复制到真正使用的范围。然后使用 AutoFilter 快速删除任何 TRUE 记录
Sub QuickKill()
Dim rng1 As Range, rng2 As Range, rng3 As Range
Set rng1 = Cells.Find("*", , xlValues, , xlByColumns, xlPrevious)
Set rng2 = Cells.Find("*", , xlValues, , xlByRows, xlPrevious)
Set rng3 = Range(Cells(rng2.Row, rng1.Column), Cells(1, rng1.Column))
Application.ScreenUpdating = False
Rows(1).Insert
With rng3.Offset(-1, 1).Resize(rng3.Rows.Count + 1, 1)
.FormulaR1C1 = "=OR(RC12=""ABC"",RC27<>""DEF"")"
.AutoFilter Field:=1, Criteria1:="TRUE"
.EntireRow.Delete
On Error Resume Next
'in case all rows have been deleted
.EntireColumn.Delete
On Error GoTo 0
End With
Application.ScreenUpdating = True
End Sub
回答by Patrick Honorez
Using a loop:
使用循环：
Sub test()
Dim x As Long, lastrow As Long
lastrow = Cells(Rows.Count, 1).End(xlUp).Row
For x = lastrow To 1 Step -1
If Cells(x, 12).Value = "ABC" or Cells(x, 27) <> "DEF" Then
Rows(x).Delete
End If
Next x
End Sub
Using autofilter(untested - probably faster):
使用自动过滤器（未经测试 - 可能更快）：
Sub test2()
Range("a1").AutoFilter Field:=12, Criteria1:="ABC", Operator:=xlOr, _
Field:=28, Criteria1:="<>""DEF"""
'exclude 1st row (titles)
With Intersect(Range("a1").CurrentRegion, _
Range("2:60000")).SpecialCells(xlCellTypeVisible)
.Rows.Delete
End With
ActiveSheet.ShowAllData
End Sub
回答by Metaller
Cell with number 12 is "L" and number 27 is "AA"
编号为 12 的单元格为“L”，编号为 27 的单元格为“AA”
Dim x As Integer
x = 1
Do While x <= ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row
If (Cells(x, 12) = "ABC") Then
ActiveSheet.Rows(x).Delete
Else
If (Cells(x, 27) <> "DEF") And (Cells(x, 27) <> "") Then
ActiveSheet.Rows(x).Delete
Else
x = x + 1
End If
End If
Loop
End Sub
回答by Jon49
Sub test()
Dim bUnion As Boolean
Dim i As Long, lastrow As Long
Dim r1 As Range
Dim v1 As Variant
lastrow = Cells(Rows.Count, 1).End(xlUp).Row
v1 = ActiveSheet.Range(Cells(1, 12), Cells(lastrow, 27)).Value2
bUnion = False
For i = 1 To lastrow
If v1(i, 1) = "ABC" Or v1(i, 16) <> "DEF" Then
If bUnion Then
Set r1 = Union(r1, Cells(i, 1))
Else
Set r1 = Cells(i, 1)
bUnion = True
End If
End If
Next i
r1.EntireRow.Delete
End Sub
