首先，让我明确地说循环没有任何问题 - 它们当然有它的位置！

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(编辑 菜单上的转到...)然后单击特殊按钮以显示SpecialCells 对话框.

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

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 突出显示 A 列，以防您不想删除整行，而是想放入一些文本或清除它

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, 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

菲利普