Instr with If语句

Question

有没有人能指出我哪里错了？

如果某些行无法满足以下代码中提供的条件，我希望将其删除。

我尝试过使用If语句和Instr函数进行循环，但没有成功。

Dim Firstrow As Integer
Dim Lastrow As Integer
Dim Lrow As Integer
Dim celltxt As String

Firstrow = 1
Lastrow = Cells(Rows.Count, 1).End(xlUp).Row

For Lrow = Lastrow To Firstrow Step -1

    If InStr(Lrow, Range("ED" & Lrow), "FTOP") > 0 Then Sheets(Sheet1).Rows(Lrow).Delete

    If InStr(Lastrow, LCase(Range("DT" & Lrow)), "BB") > 0 Then Sheets(Sheet1).Rows(Lrow).Delete

    If Sheets(Sheet1).Cells(Lrow, "DX").Value = "SET" Then Sheets(Sheet1).Rows(Lrow).Delete

    If Sheets(Sheet1).Cells(Lrow, "EM").Value = "=*AAA" Then Sheets(Sheet1).Rows(Lrow).Delete

Next Lrow

无错误消息

Answer 1

我已经修改了您的宏以遍历从Lastrow到第2行的每一行，如果在该行的四列中的任何一列中找到值或字符串部分，则该行将被删除。使用OR将所有IF语句合并为一个语句。从INSTR函数中删除Lrow和Lastrow，并将AAA放入INSTR函数中。Lrow很混乱，所以我删除了i变量，并将其用于循环。还添加了工作簿/工作表引用变量。

Sub DeleteRowsThatMeetCriteria()
    'Declare your variables
    Dim ws As Worksheet, Lastrow As Long, i As Long

    Set ws = ThisWorkbook.Sheets("Sheet1") 'Set the worksheet
    Lastrow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row 'Assign the last row varaible

        For i = Lastrow To 2 Step -1 'Assign "i" as a long number to loop from the last row to row 2

        'Because you are testing different columns for different value and performing the same delete function,
        'you can combine each test using OR, so you only need one IF statement. I removed "Lrow and Lastrow" variables
        'because they were causing confusion and not needed inside the INSTR function. Since "SET" was the only full word
        'i did not change it. But I used INSTR for "AAA" since it was part of a string.

        If InStr(ws.Range("DT" & i), "BB") <> 0 _
            Or ws.Range("DX" & i).Value = "SET" _
            Or InStr(ws.Range("ED" & i), "FTOP") <> 0 _
            Or InStr(ws.Range("EM" & i), "AAA") <> 0 Then
            ws.Rows(i).Delete
        End If

    Next i 'DaLoop
End Sub