如果单元格不包含“@”，则可以全面删除整行 [英] Efficient way to delete entire row if cell doesn't contain '@'

问题描述

我正在创建一个快速子邮箱来进行电子邮件的有效性检查。我想删除E列中不包含@的整个联系人数据行。我使用下面的宏，但它运行得太慢，因为Excel删除后移动所有行。

I'm creating a fast sub to do a validity check for emails. I want to delete entire rows of contact data that do not contain a '@' in the 'E' Column. I used the below macro, but it operates too slowly because Excel moves all the rows after deleting.

我尝试过另一种技术：设置rng = union（rng，c.EntireRow），然后删除整个范围，但是我无法防止错误消息。

I've tried another technique like this: set rng = union(rng,c.EntireRow), and afterwards deleting the entire range, but I couldn't prevent error messages.

我也尝试过将每一行添加到选择中，并且在所有选择之后（如ctrl + select），然后删除它，但是我找不到适当的语法。

I've also experimented with just adding each row to a selection, and after everything was selected (as in ctrl+select), subsequently deleting it, but I could not find the appropriate syntax for that.

任何想法？

Sub Deleteit()
    Application.ScreenUpdating = False

    Dim pos As Integer
    Dim c As Range

    For Each c In Range("E:E")

        pos = InStr(c.Value, "@")
        If pos = 0 Then
            c.EntireRow.Delete
        End If
    Next

    Application.ScreenUpdating = True
End Sub

推荐答案

你不需要循环来做到这一点。自动过滤器效率更高。 （类似于游标与SQL中的where子句）

You don't need a loop to do this. An autofilter is much more efficient. (similar to cursor vs. where clause in SQL)

自动筛选不包含@的所有行，然后将其删除如下：

Autofilter all rows that don't contain "@" and then delete them like this:

Sub KeepOnlyAtSymbolRows()
    Dim ws As Worksheet
    Dim rng As Range
    Dim lastRow As Long

    Set ws = ActiveWorkbook.Sheets("Sheet1")

    lastRow = ws.Range("E" & ws.Rows.Count).End(xlUp).Row

    Set rng = ws.Range("E1:E" & lastRow)

    ' filter and delete all but header row
    With rng
        .AutoFilter Field:=1, Criteria1:="<>*@*"
        .Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
    End With

    ' turn off the filters
    ws.AutoFilterMode = False
End Sub

注意

• .Offset（1,0）阻止我们删除标题行


• .SpecialCells（xlCellTypeVisible）指定在自动过滤器应用后保留的行


• .EntireRow.Delete 删除除标题行以外的所有可见行

• .Offset(1,0) prevents us from deleting the title row
• .SpecialCells(xlCellTypeVisible) specifies the rows that remain after the autofilter has been applied
• .EntireRow.Delete deletes all visible rows except for the title row

逐步了解代码，您可以看到每一行的作用。在VBA编辑器中使用F8。

Step through the code and you can see what each line does. Use F8 in the VBA Editor.

这篇关于如果单元格不包含“@”，则可以全面删除整行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！