时间戳

首页 > 解决方案 > 删除多张工作表

excel - 删除多张工作表

问题描述

我有以下代码来删除多张工作表。此代码需要永远删除工作表，不知道如何加快该过程。如果有人可以帮助我，那就太好了。

Sub DeleteSelectedSheets()

Dim h As Long, RESA1() As Variant

RESA1 = Array("Upload EC", "V-UploadEC", "EC Proj Data", "Orion SA Proj Data", _
    "Orion SA Data Table", "Proj Data", "Tables our", "Qty", "Multi Sites", "Data table" _
    , "Tbls", "Match", "Cov", "Quote", "Agg Quote", "RFQ", "Contractor", "HEER", "HEER_L", _
    "Site Decl", "Post Decl", "$", "$Enl", "ESInfo", "NBB Training", "T&C", "Work Order", "Installer Contract", "Recycle", "Rent", "PM", _
    "Xero", "Xero prep", "T&C Quote", "T&C VIC", "T&C noCert", "N-Nom", "CB PM Ledger", _
    "N-A9s", "N-A10s", "V-A-Lamp-Ballast", "VEET LCP", "VEU_LCP_35", "V-B-Space", _
    "V18 tbls", "V-C-BCA", "V-Compliance", "V-other", "ESS Other", "VIC pcode")

On Error Resume Next
Application.DisplayAlerts = False

For h = LBound(RESA1) To UBound(RESA1)
    Worksheets(RESA1(h)).Delete
Next h



Application.DisplayAlerts = True
On Error GoTo 0

End Sub

标签： excelvba

解决方案

如果您绝对 100% 确定所有列出的工作表都将出现在工作簿中，那么您在删除时不需要遍历工作表，但如果不是，那么正如 BraX 在评论中提到的那样，您最好的选择是关闭屏幕更新等。

Sub DeleteSelectedSheets()

With Application
    .DisplayAlerts = False
    .ScreenUpdating = False
    .Calculation = xlCalculationManual
End With

ActiveWorkbook.Worksheets(Array("Upload EC", "V-UploadEC", "EC Proj Data", "Orion SA Proj Data", _
    "Orion SA Data Table", "Proj Data", "Tables our", "Qty", "Multi Sites", "Data table" _
    , "Tbls", "Match", "Cov", "Quote", "Agg Quote", "RFQ", "Contractor", "HEER", "HEER_L", _
    "Site Decl", "Post Decl", "$", "$Enl", "ESInfo", "NBB Training", "T&C", "Work Order", "Installer Contract", "Recycle", "Rent", "PM", _
    "Xero", "Xero prep", "T&C Quote", "T&C VIC", "T&C noCert", "N-Nom", "CB PM Ledger", _
    "N-A9s", "N-A10s", "V-A-Lamp-Ballast", "VEET LCP", "VEU_LCP_35", "V-B-Space", _
    "V18 tbls", "V-C-BCA", "V-Compliance", "V-other", "ESS Other", "VIC pcode")) _
    .Delete    'directly deleting the sheets without looping

With Application
    .DisplayAlerts = True
    .ScreenUpdating = True
    .Calculation = xlCalculationAutomatic
End With

End Sub

推荐阅读