vba 如何在Excel VBA中没有循环地删除多行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15431801/
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 multiple rows without a loop in Excel VBA
提问by Our Man in Bananas
Frequently we are asked how to delete rows based on criteria in one or more columns, and can we use a SpecialCells trick for this?
我们经常被问到如何根据一列或多列中的条件删除行，我们可以为此使用 SpecialCells 技巧吗？
回答by Our Man in Bananas
First, let me say categorically that there is nothing wrong with loops- they certainly have their place!
首先，让我明确地说，循环没有任何问题——它们当然有自己的位置！
Recently we were presented with the below situation:
最近我们遇到了以下情况：
400000 | Smith, John| 2.4 | 5.66| =C1+D1
400001 | Q, Suzy | 4.6 | 5.47| =C2+D2
400002 | Schmoe, Joe| 3.8 | 0.14| =C3+D3
Blank | | | | #VALUE
Blank | | | | #VALUE
The OP wanted to delete rows where Column A is blank, but there is a value in Column E.
OP 想要删除 A 列为空白的行，但 E 列中有一个值。
I suggest that this is an example where we could make use of SpecialCells and a temporary Error Columnto identify the rows to be deleted.
我建议这是一个示例，我们可以在其中使用 SpecialCells 和临时错误列来标识要删除的行。
Consider that you might add a column H to try and identify those rows; in that row you could use a formula like below:
考虑您可能会添加一个列 H 来尝试识别这些行；在该行中，您可以使用如下公式：
=IF(AND(A:A="",E:E<>""),"DELETE THIS ROW","LEAVE THIS ROW")
now, it is possible get that formula to put an error in the rows where I test returns True. The reason we would do this is a feature of Excel called SpecialCells.
现在，可以让该公式在我测试返回 True 的行中放置错误。我们这样做的原因是 Excel 的一个称为 SpecialCells 的功能。
In Excel select any empty cell, and in the formula bar type
在 Excel 中选择任何空单元格，然后在公式栏中键入
=NA()
Next, hit F5 or CTRL+G (Go to...on the Editmenu) then click the Specialbutton to show the SpecialCells dialog.
接下来，按 F5 或 CTRL+G（编辑菜单上的转到...），然后单击特殊按钮以显示特殊单元对话框。
In that dialog, click the radio next to 'Formulas' and underneath, clear the checkboxes so that only Errorsis selected. Now click OK
在该对话框中，单击“公式”旁边的单选按钮，并在下方清除复选框，以便仅选择“错误”。现在点击确定
Excel should have selected all the cells in the worksheet with an Error (#N/A) in them.
Excel 应该选择了工作表中的所有单元格，其中包含错误 ( #N/A)。
The code below takes advantage of this trick by creating a formula in column H that will put an #N/Ain all the rows you want to delete, then calling SpecialCells to find the rows, and clear (delete) them...
下面的代码通过在 H 列中创建一个公式来利用此技巧，该公式将在您要删除的所有行中放置一个#N/A，然后调用 SpecialCells 来查找行，并清除（删除）它们...
Sub clearCells()
'
Dim sFormula As String
'
' this formula put's an error if the test returns true,
' so we can use SpecialCells function to highlight the
' rows to be deleted!
Create a formula that will return #NAwhen the formula returns TRUE
创建一个公式，当公式返回 TRUE 时将返回#NA
sFormula = "=IF(AND(A:A="""",E:E<>""""),NA(),"""")"
Put that formula in Column H, to find the rows that are to be deleted...
将该公式放在 H 列中，以查找要删除的行...
Range("H5:H" & Range("E65536").End(xlUp).Row).Formula = sFormula
Now use SpecialCells to highlight the rows to be deleted:
现在使用 SpecialCells 突出显示要删除的行：
Range("H5:H" & Range("E65536").End(xlUp).Row).SpecialCells(xlCellTypeFormulas, xlErrors).entirerow.select
This line of code would highlight just Column A by using OFFSETin case instead of deleting the entire row, you wanted to put some text in, or clear it
这行代码将通过使用OFFSET来突出显示 A 列，以防您不想删除整行，而是想放入一些文本或清除它
Range("H5:H" & Range("E65536").End(xlUp).Row).SpecialCells(xlCellTypeFormulas, xlErrors).Offset(0, -7).select
and the below line of code will delete thhe entire row because we can :)
下面的代码行将删除整行，因为我们可以:)
Range("H5:H" & Range("E65536").End(xlUp).Row).SpecialCells(xlCellTypeFormulas, xlErrors).EntireRow.Delete shift:=xlup
' clean up the formula
Range("H5:H" & Range("E65536").End(xlUp).Row).Clear
'
End Sub
BTW, it's also possible WITH A LOOPif you really want one :)
顺便说一句，如果你真的想要一个循环，它也是可能的:)
One more thing, before Excel 2010 there was a limit of 8192 rows(I think because this feature went all the way back to 8-bit versions of Excel maybe)
还有一件事，在 Excel 2010 之前，有 8192 行的限制（我认为是因为此功能可能一直回到 8 位版本的 Excel）
The VBA legend Ron de Bruin (on whose website I first picked up this technique, among others) has something to say about this
VBA 传奇人物 Ron de Bruin（我第一次在他的网站上学习了这项技术等）对此有话要说
Philip
菲利普
回答by Toor
This will delete row numbers m to n passed through the function
这将删除通过函数传递的行号 m 到 n
Sub Delete_Multiple_Rows (m as Integer, n as Integer)
Rows(m & ":" & n).EntireRow.Delete
End Sub
回答by Beallio
Alternatively you can use auto filters:
或者，您可以使用自动过滤器：
Sub clearCells()
'
' Example code for StackOverflow post
'http://stackoverflow.com/questions/15431801/how-to-delete-multiple-rows-without-a-loop-in-excel-vba
'
Dim rngTable As Range
Dim ws As Worksheet
Dim StartCell As Range
Const ColumntoFilter1 As Integer = 1
Const FilterCriteria1 As String = "="
Const ColumntoFilter2 As Integer = 5
Const FilterCriteria2 As String = "<>"
Set ws = ActiveSheet
'Set the starting position (Top-left most position) of your data range
Set StartCell = ws.Range("A1")
'Turn off autofilter in case it's already active
ws.AutoFilterMode = False
'Define data table
Set rngTable = StartCell.CurrentRegion
'Filter and delete data
With rngTable
.AutoFilter Field:=ColumntoFilter1, Criteria1:=FilterCriteria1
.AutoFilter Field:=ColumntoFilter2, Criteria1:=FilterCriteria2
.Offset(1, 0).EntireRow.Delete
End With
'Turn filters off again
ws.AutoFilterMode = False
Set rngTable = Nothing
Set StartCell = Nothing
Set ws = Nothing
End Sub
