如何在Excel VBA中删除不带循环的多行 [英] How to delete multiple rows without a loop in Excel VBA
问题描述
通常，我们被问及如何根据一列或多列中的条件删除行，我们可以使用SpecialCells技巧吗？
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?
推荐答案
首先，我明确地说，循环没有错误 - 他们当然有自己的位置！
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
OP要删除列A为空的行，但列中有一个值E。
The OP wanted to delete rows where Column A is blank, but there is a value in Column E.
我建议这是一个例子，我们可以使用SpecialCells和一个临时的错误列来标识要删除的行。
I suggest that this is an example where we could make use of SpecialCells and a temporary Error Column to identify the rows to be deleted.
考虑您可能添加一列H来尝试识别这些行;在那一行你可以使用如下公式：
Consider that you might add a column H to try and identify those rows; in that row you could use a formula like below:
=IF(AND(A:A="",E:E<>""),"DELETE THIS ROW","LEAVE THIS ROW")
可以得到该公式在我测试返回True的行中出现错误。我们这样做的原因是Excel的一个功能叫做SpecialCells。
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.
在Excel中选择任何空单元格，并在公式栏中键入
In Excel select any empty cell, and in the formula bar type
=NA()
下一页，在编辑菜单上点击F5或CTRL + G（转到... ），然后点击特殊按钮显示特别提示对话框。
Next, hit F5 or CTRL+G (Go to... on the Edit menu) then click the Special button to show the SpecialCells dialog.
在该对话框中，单击公式旁边的收音机，清除复选框，以便只选择错误。现在点击确定
In that dialog, click the radio next to 'Formulas' and underneath, clear the checkboxes so that only Errors is selected. Now click OK
Excel应该已经选择了工作表中的所有单元格，其中包含错误（＃N / A ）。
Excel should have selected all the cells in the worksheet with an Error (#N/A) in them.
下面的代码通过在列H中创建一个公式，利用这个技巧，将在要删除的所有行中放置一个＃N / A ，然后打电话给SpecialCells查找行，并清除（删除）他们...
The code below takes advantage of this trick by creating a formula in column H that will put an #N/A in all the rows you want to delete, then calling SpecialCells to find the rows, and clear (delete) them...
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!
创建一个公式，当公式返回TRUE时，将返回 #NA / p>
Create a formula that will return #NA when the formula returns TRUE
sFormula = "=IF(AND(A:A="""",E:E<>""""),NA(),"""")"
将该公式放在H列中，要删除的行...
Put that formula in Column H, to find the rows that are to be deleted...
Range("H5:H" & Range("E65536").End(xlUp).Row).Formula = sFormula
现在使用SpecialCells突出显示要删除的行：
Now use SpecialCells to highlight the rows to be deleted:
Range("H5:H" & Range("E65536").End(xlUp).Row).SpecialCells(xlCellTypeFormulas, xlErrors).entirerow.select
这一行代码将通过使用 OFFSET ，而不是删除整行，您想要放入一些文本，或清除
This line of code would highlight just Column A by using OFFSET in case instead of deleting the entire row, you wanted to put some text in, or clear it
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，如果您真的想要一个，也可以 WITH A LOOP ：）
BTW, it's also possible WITH A LOOP if you really want one :)
还有一件事，在Excel 2010之前有8192行的限制（我认为因为这个功能一直返回到8位版本的Excel可能）
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)
VBA传奇人物罗恩·德布鲁恩（Ron de Bruin（在其网站上我首先收录了这种技术，其中包括）有关于这一点的说法
The VBA legend Ron de Bruin (on whose website I first picked up this technique, among others) has something to say about this
Philip
这篇关于如何在Excel VBA中删除不带循环的多行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
