excel - 清除单元格内容

Question

有一个程序可以清除某些单元格范围的内容。它工作正常，但速度很慢(7 分钟)。
如何加速这个程序？

Sub óäàëèòü()
    Dim book1 As Workbook
    Dim B As String
    Dim v As Long
    Dim e As Long

    B = "14"

    Set book1 = Workbooks.Open("E:\Super M\Ïðîåêò ñòàâêè\Ïîèñê ðåøåíèÿ\Óñîâ 7\Óñëîâèÿ äëÿ àíäåðäîãîâ\" + B + ".xlsm")

    For v = 1 To 14
        For e = 0 To 8
            book1.Worksheets("Ëèñò" & v).Cells(34, 26 + (e * 21)).Resize(128, 5).ClearContents
        Next e
    Next v

    book1.Save
    book1.Close

End Sub

Answer 1

提高性能的一种方法是禁用 Excel 计算和屏幕更新，如下所示，这样应用程序执行的计算更少。

Sub óäàëèòü()
    Dim book1 As Workbook
    Dim B As String
    Dim v As Long
    Dim e As Long

    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    Application.EnableEvents = False

    B = "14"

    Set book1 = Workbooks.Open("E:\Super M\Ïðîåêò ñòàâêè\Ïîèñê ðåøåíèÿ\Óñîâ 7\Óñëîâèÿ äëÿ àíäåðäîãîâ\" + B + ".xlsm")

    For v = 1 To 14
        For e = 0 To 8
            book1.Worksheets("Ëèñò" & v).Cells(34, 26 + (e * 21)).Resize(128, 5).ClearContents
        Next e
    Next v

    book1.Save
    book1.Close

    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
    Application.EnableEvents = True

End Sub