主页 / computer / 问题 / 1856245
Accepted
CorlissExcelling
Asked: 2024-09-19 20:19:52 +0800 CST

如何改进当前代码来查找特定命名的工作表是否存在并删除它们？

  • 772

我一直在做一些研究，并从两个资源中拼凑了一些代码，想知道我整理的代码是否还有改进的空间。我有一个有点笨重的旧代码。

资料来源： https ://excelchamps.com/vba/delete-sheet/#:~:text=Each%20sheet%20has%20a%20name%2C%20and%20you%20can,code%20would%20be%3A%20Sub%20vba_delete_sheet%28%29%20Sheets%28%22Data%22%29.Delete%20End%20Sub

https://www.automateexcel.com/vba/delete-clear-worksheet/#:~:text=With%20On%20Error%20Resume%20Next%20you%20can%20tell,if%20so%20delete%20it.%3A %20If%20RangeExists%28%22Sheet1%22%29%20Then%20Sheet%28%22Sheet1%22%29.删除

代码：

Dim ws As Worksheet
Dim mySheet1 As Variant
Dim mySheet2 As Variant
Dim mySheet3 As Variant
Dim mySheet4 As Variant
Dim mySheet5 As Variant


mySheet1="S363"
mySheet2="non"
mySheet3="promos"
mySheet4="All Filtered"
mySheet5="Final Send"

Application.DisplayAlerts = False
For Each ws In ThisWorkbook.Worksheets
    If mySheet1 = ws.Name Then
      ws.Delete
 ElseIf  mySheet2 = ws.Name Then
      ws.Delete
ElseIf  mySheet3= ws.Name Then
      ws.Delete
ElseIf  mySheet4 = ws.Name Then
      ws.Delete
ElseIf  mySheet5 = ws.Name Then
      ws.Delete
    End If
Next ws

‘All other instructions here
Application.DisplayAlerts = True
End Sub
microsoft-excel

1 个回答

  1. Best Answer

    你可以尝试这个代码：

    Sub DelShs()
    Dim sh As Variant
    Application.DisplayAlerts = False
    On Error Resume Next
    For Each sh In Array("S363", "non", "promos", "All Filtered", "Final Send")
        Worksheets(sh).Delete
    Next sh
    On Error GoTo 0
    Application.DisplayAlerts = True
End Sub

    如果您确定所有指定的工作表都存在，则可以一次性删除它们：

    Worksheets(Array("S363", "non", "promos", "All Filtered", "Final Send")).Delete
    • 0

相关问题