How to fix run time 1004: No cells were found error

Question

The code below (finding the last row) had been working fine before, but it started making the

run time error 1004

(3rd line). I've read similar post here, but would like to fix it on this specific code.

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

It is like that, probably because You don't have any `Constants` in Column A. Put there any values or use Error Handling - `On Error Resume Next` — Teamothy, Nov 18 '19 at 08:24
This is code I have [given](https://stackoverflow.com/q/58751050/9758194) you. It would be decent to mention this, and even more decent to recognize the anwer I provided (questions I asked about this error) before opening a new question. — JvdV, Nov 18 '19 at 10:50

score 0 · Accepted Answer · answered Nov 18 '19 at 08:31

It looks like there's no way to trap the error in .specialcells method.

Following workaround can be used.

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

How to fix run time 1004: No cells were found error

1 Answers1