excel - 删除包含特定值 VBA 的行

Question

这个问题在这里已经有了答案:





VBA excel code to delete the rows if N/A is found

(2 个回答)



Find and delete Rows where cell value is "#N/A"

(2 个回答)


去年关闭。




我正在寻找一个简单的程序来搜索“In Shout？”列，搜索此列中的所有“#N/A”并删除这些行。
当我运行以下内容时，它不会删除行，我不知道为什么。谁能明白为什么？

Sub DeleteBadRows()

    Dim InShout As Long
    Dim NA As String
    
    NA = "#N/A"

    'Declaring year value of 1 month & 2 month
    'This is important to compare datasets from 2 months ago & last month
    Year_2M = Format(Date - 57, "YYYY")

    'Declaring month value of 1 month & 2 month
    'This is important to compare datasets from 2 months ago & last month
    Month_2M = Format(Date - 57, "MM")

    'This translates the current month from number to character format
    MonthChar_2 = MonthName(Month_2M, False)

    sheet = "MASTERFILE_" & Year_2M & Month_2M
    
    'setting string values so that we can identify open workbooks
    myFile = "Dataset"
    otherFile = "Monthly Reporting Tool"
    shoutFile = "Copy of Daily Shout"
    
        'if tool wb is open, declare it as MonthlyRepTool
    For Each wb In Application.Workbooks
        If wb.Name Like otherFile & "*" Then
           Set MonthlyRepTool = Workbooks(wb.Name)
        End If
    Next wb
    
        With MonthlyRepTool.Worksheets(sheet).Rows(1)
            Set e = .Find("In Shout?", LookIn:=xlValues)
            InShout = e.Column
        End With

    lastRow = MonthlyRepTool.Worksheets(sheet).Cells(Rows.count, "A").End(xlUp).Row

    For i = 2 To lastRow Step -1

        If MonthlyRepTool.Worksheets(sheet).Cells(i, InShout).value = NA Then
            MonthlyRepTool.Worksheets(sheet).Rows(i).EntireRow.Delete
        End If
        
    Next i

End Sub

Answer 1

For i = 2 To lastRow Step -1

应该

For lastRow  to 2 Step -1