删除Excel中的特定选项卡

贤者莫汉

我有一个包含多个工作表的工作簿。我想删除其中没有单词“ ApprovedSS”的所有选项卡。目前，我有以下代码：

Sub DeleteTabs()
Dim xWs As Worksheet
Application.ScreenUpdating = False
Application.DisplayAlerts  = False
For Each xWs In Application.ActiveWorkbook.Worksheets
    If xWs.Name <> "ApprovedSS" Then
        xWs.Delete
    End If
Next
Application.DisplayAlerts = True
Application.ScreenUpdating = True

结束子

我可以修改此代码以完成工作吗？

戴维·泽门斯

取而代之的是测试等效性（或其否定）：

If xWs.Name <> "ApprovedSS" Then
    xWs.Delete
End If

尝试执行此操作，以测试工作表的名称是否包含 "ApprovedSS"：

If Instr(xWs.Name, "ApprovedSS") > 0 Then
    xWS.Delete
End If

要处理多张工作表，请首先构建要保留的工作表名称的分隔列表，将其拆分为一个数组，然后使用该Match功能测试每个工作表名在数组中的存在。

这将测试变量中列出的工作表名称是否完全匹配KEEP_LIST。

Option Explicit

Sub DeleteSheets()
Dim KEEP_LIST As String
Dim keepSheets
Dim ws As Worksheet

KEEP_LIST = "ApprovedSS,ApprovedRT" '## Add items to this list, separated by comma
keepSheets = Split(KEEP_LIST, ",")

For Each ws In ThisWorkbook.Worksheets
    'You can't delete the last sheet in a book, so don't even try.
    If ThisWorkbook.Worksheets.Count = 1 Then Exit For
    ' if the sheetname doesn't exist in our KEEP_LIST,
    ' then we delete this worksheet
    If IsError(Application.Match(ws.Name, keepSheets, False)) Then
        Application.DisplayAlerts = False
        ws.Delete
        Application.DisplayAlerts = True
    End If
Next
End Sub

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。