vba 查找并删除单元格值为“#N/A”的行

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

提问by risail

I have an excel document that I use to analyze data sets each data asset I bring in has varying amounts of data. I have tried to write a macro that I assign to a button that can identify delete rows based on the value of a cell. It does not work. What am I doing wrong?

我有一个 excel 文档，我用它来分析数据集，我引入的每个数据资产都有不同数量的数据。我试图编写一个分配给按钮的宏，该按钮可以根据单元格的值识别删除行。这是行不通的。我究竟做错了什么？

Sub Button2_Click()
[vb]
'This will find how many rows there are
 With ActiveSheet
    lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
    MsgBox lastRow
 End With

 Sub sbDelete_Rows_Based_On_Criteria()
     Dim lRow As Long
     Dim iCntr As Long
     lRow = lastRow
     For iCntr = lRow To 1 Step -1
         'Replaces XX with the variable you want to delete
         If Cells(iCntr, 1) = "#N/A" Then
             Rows(iCntr).Delete
         End If
     Next
 End Sub
 [/vb]
End Sub

回答by Jason Faulkner

Your logic is pretty much there, but your syntax is off. Additionally, you are onlychecking column A for the value and not column B (per your comments above).

您的逻辑几乎就在那里，但您的语法已关闭。此外，您只检查 A 列的值，而不检查 B 列（根据您上面的评论）。

Sub Button2_Click()
    Dim lRow As Long
    'This will find how many rows there are
    With ActiveSheet
        lRow = .Cells(.Rows.Count, "A").End(xlUp).Row
        MsgBox lastRow
    End With

    Dim iCntr As Long
    For iCntr = lRow To 1 Step -1
        'Replace "#N/A" with the value you want to delete
        ' Check column A and B for the value.
        If Cells(iCntr, 1).Text = "#N/A" Or Cells(iCntr, 2).Text = "#N/A" Then
            Rows(iCntr).Delete
        End If
    Next
End Sub

Or simplified:

或简化：

Sub Button2_Click()
    Dim iCntr As Long
    For iCntr = Cells(Rows.Count, "A").End(xlUp).Row To 1 Step -1
        'Replace "#N/A" with the value you want to delete
        ' Check column A and B for the value.
        If Cells(iCntr, 1).Text = "#N/A" Or Cells(iCntr, 2).Text = "#N/A" Then
            Rows(iCntr).Delete
        End If
    Next
End Sub

回答by Gary's Student

Because you have two subs, you must pass lastRowfrom one to the other:

因为你有两个 sub，你必须将lastRow从一个传递到另一个：

Sub Button2_Click()
'This will find how many rows there are
 With ActiveSheet
    lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
    MsgBox lastRow
End With

Call sbDelete_Rows_Based_On_Criteria(lastRow)

End Sub

 Sub sbDelete_Rows_Based_On_Criteria(lastRow)
 Dim lRow As Long
 Dim iCntr As Long
 lRow = lastRow
 For iCntr = lRow To 1 Step -1
    'Replaces XX with the variable you want to delete
    If Cells(iCntr, 1).Text = "#N/A" Then
        Rows(iCntr).Delete
    End If
 Next
 End Sub

Note:

笔记：

1. the sub are unnested
2. Use .Text

1. 子是未嵌套的
2. 使用.Text