VBA Excel宏删除带字符串的工作表

VBA Excel Macro Delete Sheets With String

提问by Samiko

I have used a bit of VBA code to search through all sheet names in a workbook for a certain string, lets call it "Text". When it finds a sheet with that string it should delete that sheet. But lets say there are four sheets with "Text"in the name (named text 1, text 2, text 3and text 4), instead of deleting all four, it deletes Text 1and Text 3. It leaves the 2nd and 4th as non-deleted. Then if I call the macro again it deletes Text 2but leaves Text 4. Finally if I click it again it deletes Text 4. I cannot work out why as it looks like it should work.

我使用了一些 VBA 代码在工作簿中的所有工作表名称中搜索某个字符串，我们称之为"Text"。当它找到带有该字符串的工作表时，它应该删除该工作表。但是假设有四个名称中带有“Text”的工作表（命名为text 1、text 2、text 3和text 4），而不是删除所有四个，而是删除Text 1和Text 3。它将第二个和第四个保留为未删除。然后，如果我再次调用宏，它会删除Text 2但留下Text 4。最后，如果我再次单击它会删除文本 4。我不知道为什么它看起来应该可以工作。

    Dim i As Integer, n As Integer
    n = ThisWorkbook.Worksheets.Count
    i = 1
    Application.DisplayAlerts = False
    Application.ScreenUpdating = False
    Do
        On Error Resume Next
        If InStr(1, Sheets(i).Name, "Text") Then Sheets(i).Delete
        On Error GoTo 0
        i = i + 1
    Loop Until i = n
    Application.DisplayAlerts = True

    Application.ScreenUpdating = True

回答by Rory

You need to loop backwards to avoid skipping sheets:

您需要向后循环以避免跳过工作表：

Dim i As Integer, n As Integer
n = ThisWorkbook.Worksheets.Count

Application.DisplayAlerts = False
Application.ScreenUpdating = False
For i = n to 1 step -1
    On Error Resume Next
    If InStr(1, Sheets(i).Name, "Text") Then Sheets(i).Delete
    On Error GoTo 0
Next i
Application.DisplayAlerts = True

Application.ScreenUpdating = True

Otherwise, if the code deletes sheet 1, sheet 2 becomes sheet 1, but i is incremented to 2 and so the original sheet 2 is never processed.

否则，如果代码删除工作表 1，工作表 2 成为工作表 1，但 i 增加到 2，因此永远不会处理原始工作表 2。

回答by Thundereagle

I am not a VBA pro, but this might work as well if you want to give it a shot ;)

我不是 VBA 专家，但如果您想试一试，这也可能有效；)

Dim WS As Worksheet
Application.DisplayAlerts = False
Application.ScreenUpdating = False
For Each WS In Worksheets
' Specify the "TEXT" you are looking for in the sheet name in uppercase!
    If InStr(UCase(WS.Name), "TEXT") Then WS.Delete
    On Error GoTo 0
Next WS
Application.DisplayAlerts = True
Application.ScreenUpdating = True

回答by Noldor130884

You could also decrease n and not increase i as a Sheet is deleted:

您也可以减少 n 而不是增加 i 作为工作表被删除：

Dim i As Integer, n As Integer
n = ThisWorkbook.Worksheets.Count
i = 1
Application.DisplayAlerts = False
Application.ScreenUpdating = False
Do
    On Error Resume Next
    If InStr(1, Sheets(i).Name, "Text") Then 
        Sheets(i).Delete
        n = n-1
    Else
        On Error GoTo 0
        i = i + 1
    End If
Loop Until i = n
Application.DisplayAlerts = True

Application.ScreenUpdating = True