1004, Application-defined or Object-defined error when setting a Range as a variable with `.End(xlDown)` function

Question

I've wrote a code to delete rows which cell in column five has a value of 0:

Sub deletedata()

Dim i As Integer
Dim n As Integer
Dim ws3 As Worksheet
Set ws3 = ThisWorkbook.Worksheets("3.Calidad CREDITICIA")
Dim r As Range
Set r = ws3.Range("E20", Range("E20").End(xlDown))
n = r.Cells.Count

With ws3
         For i = n + 20 To 20
            If IsNumeric(.Cells(i, 5)) And .Cells(i, 5) = 0 Then
                .Rows(i & ":" & i).Delete
            End If
            Next i
    End With

End Sub

When it arrives to the instruction in which I set r range: Set r = ws3.Range("E20", Range("E20").End(xlDown)) I get the 1004 error:

1004, Application-defined or Object-defined

I've tried so many things, between them I tried a MyCell loop, but I get still the error when setting range ¿Could someone tell me what am I doing wrong?

Is the worksheets protected? Or might the VBA code be on the sheet as opposed to be in a module? — tk78
The code is in Thisworkbook instead of a module, Would you explain me why that fact could make this code not to work? — Mauro
I can't give you a detailed explanation. But I have experienced this in my VBA Projects. — tk78

Brandon Barney Brandon Barney · Accepted Answer · 2018-01-24T13:11:42

The problem likely has to do with the implicit reference to ActiveSheet:

                        ' Right here
Set r = ws3.Range("E20", Range("E20").End(xlDown))

Try this instead:

Set r = ws3.Range("E20", ws3.Range("E20").End(xlDown))

1004, Application-defined or Object-defined error when setting a Range as a variable with `.End(xlDown)` function

1 Answers