CODEWITHSUNDEEP
vbaexcel
Samiko
Samiko

Reputation: 191

VBA Excel Macro Delete Sheets With String

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 3 and text 4), instead of deleting all four, it deletes Text 1 and Text 3. It leaves the 2nd and 4th as non-deleted. Then if I call the macro again it deletes Text 2 but 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.

    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

Upvotes: 5

Views: 11800

Answers (3)

Thundereagle
Thundereagle

Reputation: 153

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

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

Upvotes: 3

Noldor130884
Noldor130884

Reputation: 1004

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

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

Upvotes: 2

Rory
Rory

Reputation: 34075

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.

Upvotes: 13

Related Questions