VBA运行时错误1004：动态选取Range清空内容报错排查

问题现象 #

编写VBA宏执行清空列内容操作时触发运行时错误1004（应用程序定义或对象定义错误）：

• 动态范围写法Wks_So.Range("A9", Range("A9").End(xlDown)).ClearContents运行报错
• 替换为固定范围写法Wks_So.Range("A9:A150").ClearContents时可正常运行
• 预期目标：清空A列从A9单元格起始，到该列最后一个有值单元格范围内的所有内容

初始问题代码片段 #

Dim i As Integer
Dim j As Integer
Dim x As Integer

Application.Calculation = xlCalculationManual

Set Wks_Sb = Worksheets("Scarico_Bond")
Set Wks_So = Worksheets("Scarico_Other")
Set Wks_I = Worksheets("Invio")

Wks_Sb.Range("A9", Range("A9").End(xlDown)).ClearContents
'Wks_Sb.Range("A9:A150").ClearContents
Wks_So.Range("A9", Range("A9").End(xlDown)).ClearContents

关联完整业务代码 #

'Wks_So.Range("A9:A50").ClearContents
Wks_Sb.Range("D9:D140").Interior.Color = vbWhite

j = 9
k = 9
x = 8

For i = 7 To 150

    If InStr(1, (Wks_I.Cells(i, 4).Value), "Obbligazioni") > 0 Then
        
        Wks_Sb.Cells(j, 1) = x - 7
        j = j + 1
        
        Else
            
            If InStr(1, (Wks_I.Cells(i, 4).Value), "Fondi/ETF") > 0 Then
            
                Wks_So.Cells(k, 1) = x - 7
                k = k + 1
        
            End If
    End If

    x = x + 1

Next i
    
Application.Calculation = xlCalculationAutomatic

End Sub

错误原因 #

核心问题是Range对象未明确指定父工作表：

• 写法Wks_So.Range("A9", Range("A9").End(xlDown))中，第一个参数A9明确属于Wks_So工作表，但第二个参数Range("A9").End(xlDown)没有加工作表限定，默认会指向当前活动工作表（ActiveSheet）。
• 当代码执行时活动工作表不是Scarico_Other，Range的两个边界参数分属不同工作表，VBA无法构造跨表的连续范围，直接抛出1004错误。
• 额外隐患：如果A9单元格下方没有任何已填写内容，End(xlDown)会直接跳转到A列最底部行（Excel 2007及以后版本为第1048576行），不仅清空效率极低，还可能误删后续需要保留的内容。

修复方案 #

1. 所有Range/Cells对象统一绑定到目标工作表，避免默认取活动工作表的问题
2. 改用「从列底部向上查找最后一行」的逻辑，替代End(xlDown)，避免空值跳转到整列底部的问题
3. 增加空范围判断，当A9及以下没有内容时跳过清空操作，避免无效报错

修复后的清空逻辑代码 #

Dim lastRowSb As Long, lastRowSo As Long
' 处理Scarico_Bond表A列清空
lastRowSb = Wks_Sb.Cells(Wks_Sb.Rows.Count, "A").End(xlUp).Row
If lastRowSb >= 9 Then
    Wks_Sb.Range("A9:A" & lastRowSb).ClearContents
End If

' 处理Scarico_Other表A列清空
lastRowSo = Wks_So.Cells(Wks_So.Rows.Count, "A").End(xlUp).Row
If lastRowSo >= 9 Then
    Wks_So.Range("A9:A" & lastRowSo).ClearContents
End If

更稳妥的书写规范 #

可以用With块简化工作表限定的书写，避免漏写父对象：

With Wks_So
    lastRowSo = .Cells(.Rows.Count, "A").End(xlUp).Row
    If lastRowSo >= 9 Then .Range("A9:A" & lastRowSo).ClearContents
End With

块内带.前缀的单元格、范围对象，都会自动绑定到With后指定的工作表，不会出现串到活动表的问题。

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