excel - 如何修复运行时1004 : No cells were found error

Question

下面的代码(查找最后一行)以前工作正常，但是开始使

run time error 1004

(第三行)。我在这里读过类似的文章，但想在此特定代码上修复它。

With Sheet1.Range("A:A")

    If Application.WorksheetFunction.CountA(.Cells) <> 0 Then
        ' next line is where it errors
        Set rng = .SpecialCells(xlCellTypeConstants, 1)
        Set rng = rng.Areas(rng.Areas.Count)
        lastrow = rng.Cells(rng.Cells.Count).Row
    End If

End With

Answer 1

似乎无法在.specialcells方法中捕获错误。

可以使用以下解决方法。

With Sheet1.Range("A:A")
    If Application.WorksheetFunction.CountA(.Cells) <> 0 Then
        ' next line is where it errors
        On Error Resume Next 'Avoid Error Call
        Set Rng = .SpecialCells(xlCellTypeConstants, 1)
        On Error GoTo 0 'Reset Error Ignoring Behavior
        If Not Rng Is Nothing Then
            Set Rng = Rng.Areas(Rng.Areas.Count)
            lastrow = Rng.Cells(Rng.Cells.Count).Row
        End If
    End If
End With