使用.End(xlDown)设置Range变量时出现1004错误求助

Hey Mauro, let's break down why that 1004 error is hitting you when setting your range—it's a common gotcha with VBA worksheet references!

The Root Cause #

Your problem is in this line:

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

The second Range("E20") doesn't have the ws3. qualifier. VBA defaults to using the currently active worksheet for unqualified range references. If that active sheet isn't "3.Calidad CREDITICIA", you're trying to create a range that spans two different worksheets—which isn't allowed, hence the 1004 error.

Quick Fix for the Range Line #

Just add the ws3. qualifier to the second range reference:

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

Even Safer Approach #

If there's no data below E20, End(xlDown) will jump to the very last row of the sheet, which can create an unnecessarily huge range. A better way is to find the last used row in column E first:

Dim lastRow As Long
lastRow = ws3.Cells(ws3.Rows.Count, 5).End(xlUp).Row
Set r = ws3.Range("E20:E" & lastRow)

Full Revised Code #

I also made a couple of small improvements (like using Long instead of Integer to avoid overflow errors with large datasets):

Sub deletedata()
 Dim i As Long ' Use Long instead of Integer—Excel has too many rows for Integer limits
 Dim lastRow As Long
 Dim ws3 As Worksheet
 Set ws3 = ThisWorkbook.Worksheets("3.Calidad CREDITICIA")
 
 ' Get the last used row in column E
 lastRow = ws3.Cells(ws3.Rows.Count, 5).End(xlUp).Row
 
 ' Loop from bottom to top (you had this right—critical for deleting rows!)
 With ws3
 For i = lastRow To 20 Step -1
 If IsNumeric(.Cells(i, 5)) And .Cells(i, 5) = 0 Then
 .Rows(i).Delete ' Simplified the row reference from .Rows(i & ":" & i)
 End If
 Next i
 End With
End Sub

Why Long Instead of Integer? #

Excel worksheets can have up to 1,048,576 rows, but the Integer data type only goes up to 32,767. Using Long prevents overflow errors if your dataset grows beyond that limit.

内容的提问来源于stack exchange，提问作者Mauro