除少数例外，如何标记或删除不在列表中的图纸 [英] How to flag or delete sheets not in a list with a few exceptions

问题描述

以下是一些正在进行的代码，用于执行以下操作:

The following is a work in progress of some code to do the following:

• 颜色表选项卡(如果它们不在参考列表中)
• 通知用户列表不在参考列表中
• 根据用户输入删除不在参考列表中的图纸

Sub Audit_Estimate_sheets()
    Dim ws As Worksheet
    Dim wb As Workbook
    Dim ws_List As String
    Dim Delete_Orphans As Integer
    Dim Item_List_Sheet As Worksheet
    Dim Item_List_First_Row As Long
    Dim Item_List_Max_Row As Long

    Set Item_List_Sheet = Sheets(2)

    Item_List_First_Row = 14
    Item_List_Max_Row = Item_List_First_Row + Application.WorksheetFunction.Max(Item_List_Sheet.Range("B:B")) - 1

    Set wb = ActiveWorkbook
    For Each ws In wb.Worksheets
        If IsError(Application.Match(ws.Name, Item_List_Sheet.Range("C" & Item_List_First_Row & ":C" & Item_List_Max_Row), 0)) And ws.Index > 2 Then
            'Colour Tab'
            With ws.Tab
                .ThemeColor = xlThemeColorAccent2
                .TintAndShade = 0
            End With
            'Add name to list
            If ws_List = "" Then
                ws_List = ws.Name
            Else
                ws_List = ws_List & ", " & ws.Name
            End If
        End If
    Next ws

    'display list
    Delete_Orphans = MsgBox("The following estimate sheets were not part of the item list and are currently orphaned:  " & vbLf & vbLf & ws_List & vbLf & vbLf & "Would you like to delete them?", vbYesNo + vbQuestion, "Delete Orphaned Estimates")

    If Delete_Orphans = vbYes Then
        'loop through sheets again and delete
    End If

End Sub

在我的工作簿中，有几张表不在参考列表中，但我也不想删除它们.如果我的理解是:如果用户无序拖动选项卡，其索引号也会改变，那么即使是确保我超出sheet(2)的方法也不一定是一种好方法.我处理大量文本的简单方法是一系列嵌套if语句

In my workbook there are a few sheets that are not in the reference list but I do not want them deleted either. Even my approach of making sure I am beyond sheet(2) is not necessarily a good approach if my understanding that if a user drags a tab out of order its index number can change. My simple approach which winds up with a lot of text is a series nested if statements

IF ws.name <> exception1 Then
    IF ws.name <> exception2 Then
        IF ws.name <> exception3 Then
            ws.delete
        End If
    End If
End If

有更好的方法吗?

推荐答案

我认为Select Case会有所帮助:

I think Select Case will help:

Select Case ws.Name
    Case "exception1", "exception2", "exception3" 'ignore
    Case Else
       ws.Delete 'or whatever other code you want here
End Case

如果您的例外列表很长，还可以编写一个函数来根据定义的名称列表测试名称.这样会更容易管理.

If you're exception list becomes long, you can also write a function to test the name against a list of defined names as well. That will be easier to manage.

简而言之，一个函数示例就是这样:

One function example would like this, in short:

If Not exception(ws.Name) Then
    ws.Delete 'or whatever other code is needed
End If

Function exception(theSheet as String) as Boolean
'boolean is false by default, so only changing to true if it finds sheet is in exception list

    Dim exceptions(2) as String
    exceptions(0) = "exception1"
    exceptions(1) = "exception2"
    exceptions(2) = "exception3"

    Dim looper as Integer
    For looper = lbound(exceptions) to ubound(exceptions)
        If theSheet = exceptions(looper) Then
            exception = True
            Exit For
        End If
    Next

End Function

这篇关于除少数例外，如何标记或删除不在列表中的图纸的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！