vba - 试图清空单元格以绘制图表中的空白

Question

我有一系列数据，从单元格 C169:N174 运行，获取折线图。单元格数据可以是数字，也可以是空的(Excel 为空) - 公式返回数字或空白文本 ("")。

当通过数据验证触发操作时，我需要将最右侧单元格中的公式 (N169:N174) 向左复制到 C169:C174)。为此，我有以下代码:

Range("N169:N174").AutoFill Destination:=Range("C169:N174")
Range("C169:N174").Select

复制公式后，我需要清除任何包含文本(“”)的单元格。为此，我有以下代码:

Range("C169:M174").SpecialCells(xlCellTypeFormulas, 2).Select
Selection.ClearContents

此代码针对两组数据运行。相同的代码，不同的单元格。范围是 C169:N174 和 C145:N150。我首先为 range1 (C145:N15) 编写了代码 - 复制公式然后删除带有 ("") 的单元格。然后对 range2 (C169:N174) 执行相同的操作。完整代码:

Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False

If Target.Address = "$B$2" Then

'mcroCopyTrendedRentFormulas
    Range("N145:N150").AutoFill Destination:=Range("C145:N150"), Type:=xlFillDefault
    Range("C145:N150").Select

    'mcroClearTrendedRentEmptyCells
    Range("C145:N150").SpecialCells(xlCellTypeFormulas, 2).Select
    Selection.ClearContents

    'mcroCopyAskingRentFormulas
    Range("N169:N174").AutoFill Destination:=Range("C169:M174")
    Range("C169:N174").Select

    'mcroClearAskingRentEmptyCells
    Range("C169:M174").SpecialCells(xlCellTypeFormulas, 2).Select
    Selection.ClearContents

    Range("A1").Select

End If

End Sub

触发代码后，如果我在工作表完成运行之前单击工作表上的任意位置(我刚刚计时并且花了 45 秒)，我会收到一条错误消息:

"1004 Error: No cells were found"

或者

"1004 Error: AutoFill method of Range class failed".

如果我让代码运行整个 45 秒，它就可以工作。如果我单击工作表中的任意位置并收到任一错误，请停止调试器并尝试再次运行它，我会收到两个错误之一。

那么也许加快执行速度是问题所在？

我不知道——这里对任何东西都开放。

Answer 1

我稍微编辑了代码，主要去除了多余的.Select行(好吧，将它们注释掉)并合并 .Select和 .ClearContents .这应该运行得更快一些。注意 ScreenUpdating 的使用和 Calculation .

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$B$2" Then
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.EnableEvents = False

    'mcroCopyTrendedRentFormulas
    Range("N145:N150").AutoFill Destination:=Range("C145:N150"), Type:=xlFillDefault
    'Range("C145:N150").Select

    'mcroClearTrendedRentEmptyCells
    Range("C145:N150").SpecialCells(xlCellTypeFormulas).ClearContents

    'mcroCopyAskingRentFormulas
    Range("N169:N174").AutoFill Destination:=Range("C169:M174")
    'Range("C169:N174").Select

    'mcroClearAskingRentEmptyCells
    Range("C169:M174").SpecialCells(xlCellTypeFormulas).ClearContents

    Range("A1").Select

End If
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True
End Sub

我还删除了 2来自您的 SpecialCells()因为我不认为这是必需的，并且在我删除它之前也为我抛出了一个错误。

在运行时，让它运行而不单击。实际上，当任何宏在 Excel 中运行时，最好让它静置并运行。