VBA Excel - 有没有办法根据两列中的条件删除行？

VBA Excel - Is there a way to delete rows based on criteria in two columns?

提问by user2170801

I've been working on a macro to clean up a form generated and copied into Excel. I have everything figure out up until the last stage where I need to delete the rows that contain extranious formulas. Example:

我一直在研究一个宏来清理生成并复制到 Excel 中的表单。在我需要删除包含无关公式的行的最后阶段之前，我已经弄清楚了一切。例子：

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

Is there a formula to compare column 'A' to column 'E' and have it delete all rows with no value in 'A', but a value in 'E'?

是否有公式可以将“A”列与“E”列进行比较，并删除“A”中没有值但“E”中有值的所有行？

I've tried a basic loop formula going solely by the empty 'A' column which worked to the point of forever deleting rows below the information I want to keep, and I found a formula dealing with filters on this website (from brettdj) but I did not know how to edit it to properly capture what I needed.

我已经尝试了一个基本的循环公式，它只通过空的“A”列运行，它可以永久删除我想要保留的信息下方的行，并且我在这个网站上找到了一个处理过滤器的公式（来自 brettdj）但是我不知道如何编辑它以正确捕获我需要的内容。

This is what I have from brettdj:

这是我从 brettdj 得到的：

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(RC1="",RC1<>"")"
    .AutoFilter Field:=2, 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

Some other information:

一些其他信息：

The first 5 rows of the sheet I want to ignore, they hold column titles and the report name, etc.

我想忽略的工作表的前 5 行，它们包含列标题和报告名称等。

The number of rows filled by 40####'s changes, so I can't simply have the macro delete row 4 to whatever.

由 40#### 更改填充的行数，所以我不能简单地让宏删除第 4 行。

I don't have extensive knowledge of macro's (obviously) so detailed explanations of how certain portions work would be appreciated. Thanks in advance for any help.

我对宏（显然）没有广泛的了解，因此对某些部分如何工作的详细解释将不胜感激。在此先感谢您的帮助。

回答by Our Man in Bananas

yes there is a way that doesn't need a loop

是的，有一种不需要循环的方法

your formula (in Excel spreadsheet) would be something like this:

您的公式（在 Excel 电子表格中）将是这样的：

=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!
sFormula = "=IF(AND(A:A="""",E:E<>""""),NA(),"""")"
'
' put a formula into column H, to find the rows that ned to be deleted...
Range("H5:H" & Range("E65536").End(xlUp).Row).Formula = sFormula
'
Stop ' next line clears multiple rows, could also delete the entire row!
' now clear the cells returned by SpecialCells ...
Range("H5:H" & Range("E65536").End(xlUp).Row).SpecialCells(xlCellTypeFormulas, xlErrors).Offset(0, -7).Clear ' ' EntireRow .Delete shift:=xlup
'
' clean up the formula
Range("H5:H" & Range("E65536").End(xlUp).Row).Clear    '
    End Sub

copy paste that and step through using F8 ...

复制粘贴并使用 F8 逐步完成...

then we can help you some more...

那么我们可以为您提供更多帮助...

Hope that gets you started

希望能让你开始

BTW, it's also possible WITH A LOOPif you really want one :)

顺便说一句，如果你真的想要一个循环，它也是可能的:)

Philip

菲利普

回答by StoriKnow

I don't know why using loops was made out to be a bad thing by Philip. Here's a simple macro that goes through every row and deletes rows where Column Ais empty and Column Ehas some value.

我不知道为什么 Philip 认为使用循环是一件坏事。这是一个简单的宏，它遍历每一行并删除Column A空行并Column E具有某些值的行。

It makes the assumption that your code is on Sheet 1. I've commented the code to make it easier for you to follow. If you don't want to consider rows 1-5for deletion, then set the For loopvalue to 6as the comment in the code says.

它假设您的代码在Sheet 1. 我已经对代码进行了注释，以便您更容易理解。如果您不想考虑1-5删除行，则将For loop值设置6为代码中的注释。

Note that the deletion occurs from the bottom up because when you delete a row the totalRowscount changes in the loop. To avoid the loop from missing rows I had it simply go in reverse order.

请注意，删除是自下而上进行的，因为当您删除一行totalRows时，循环中的计数会发生变化。为了避免丢失行的循环，我只是按相反的顺序进行。

CODE

代码

Public Sub DeleteWhereEmpty()
    Dim colARowCount As Integer
    Dim colERowCount As Integer
    Dim totalRows As Integer
    Dim currentRow As Integer
    Dim colA As Integer
    Dim colE As Integer
    Dim colACurrentValue As String
    Dim colECurrentValue As String

    colA = 1
    colE = 5
    colARowCount = Sheet1.Cells(Rows.Count, colA).End(xlUp).Row
    colERowCount = Sheet1.Cells(Rows.Count, colE).End(xlUp).Row

    'set total number of rows to look at
    totalRows = colARowCount
    If colARowCount < colERowCount Then
        totalRows = colERowCount 'E has more rows, so use it
    End If

    'to stop deleting at row 5, replace 'To 1' with 'To 6'
    For currentRow = totalRows To 1 Step -1
        colACurrentValue = Cells(currentRow, colA).Value
        colECurrentValue = Cells(currentRow, colE).Text

        'if A is empty and E has data in it, delete the row
        If colACurrentValue = "" Or IsEmpty(colACurrentValue) _
           And Len(colECurrentValue) > 0 Then
            Cells(currentRow, colA).EntireRow.Delete
        End If
    Next
End Sub

BEFORE

前

AFTER

后