删除Excel中没有特定文本的整行

I am trying to delete the entire rows that do not have "AB" or "ON" in ID field in column A. I have written the code below but it deletes all the rows. Can anyone assist me with this please. I have attached the screenshot of the data, Thanks! Data Screenshot link:

我试图删除列A中ID字段中没有“AB”或“ON”的整行。我已经编写了下面的代码,但它删除了所有行。任何人都可以帮助我。我附上了数据截图,谢谢!数据截图链接:

Sub DeleteRows()

FinalRow = Cells(Rows.Count, "A").End(xlUp).Row

i = 2

Do While i <= FinalRow

    If Cells(i, "A") <> "PQ*" OR If Cells(i, "A") <> "ON*" Then

        Cells(i, 1).EntireRow.Delete

        FinalRow = FinalRow - 1

    Else

        i = i + 1

    End If

Loop

End Sub

3 个解决方案

#1

As per my Comments:

根据我的评论:

Sub DeleteRows()

FinalRow = Cells(Rows.Count, "A").End(xlUp).Row

i = 2

Do While i <= FinalRow

    If Left(Cells(i, "A"), 2) = "PQ" Or Left(Cells(i, "A"), 2) = "ON" Then

        i = i + 1

    Else

        Cells(i, 1).EntireRow.Delete

        FinalRow = FinalRow - 1


    End If

Loop

End Sub

#2

When deleting rows, you are better off going from the bottom up

删除行时,最好从下往上

Wild cards cannot be used with the equality operators; you should be using the Likeoperator.

通配符不能与相等运算符一起使用;你应该使用Likeoperator。

Your logic is off a bit. You need to enclose the OR in parentheses

你的逻辑有点偏。您需要将OR括在括号中

If you are using the wild card as I understand it in your original post, it translates to cells that start with PQ or ON

如果您使用的是我在原始帖子中理解的外卡,它会转换为以PQ或ON开头的单元格

So:

Option Explicit
Sub DeleteRows()
    Dim FinalRow As Long
    Dim I As Long

FinalRow = Cells(Rows.Count, "A").End(xlUp).Row

For I = FinalRow To 2 Step -1
    If Not (Cells(I, "A") Like "PQ*" Or _
        Cells(I, "A") Like "AB*") Then _
        Cells(I, 1).EntireRow.Delete
Next I

End Sub

#3

Here is an option that is more similar to what you were doing.

这是一个与您正在做的更相似的选项。

EDITED: Should work if the ID is the first two letters.

编辑:如果ID是前两个字母,应该工作。

Sub Deleterows()

FinalRow = Cells(Rows.count, "A").End(xlUp).row

i = 2

Do While i <= FinalRow

If Not Left(Cells(i, "A"), 2) = "PQ" And Not Left(Cells(i, "A"), 2) = "ON" Then

    Cells(i, 1).EntireRow.Delete

    FinalRow = FinalRow - 1

Else

    i = i + 1

End If

Loop

End Sub

#1