VBA Excel Macro Delete Sheets With String

Question

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

Rory · Accepted Answer · 2014-07-17 08:34:49Z

13

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.

answered Jul 17, 2014 at 8:34

Rory

34.2k5 gold badges40 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Thundereagle · Accepted Answer · 2014-07-17 09:46:24Z

3

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

answered Jul 17, 2014 at 9:46

Thundereagle

1531 gold badge1 silver badge10 bronze badges

1 Comment

Samiko Over a year ago

This does work so thank you but i ticked Rory's because it made more sense to me.

Noldor130884 · Accepted Answer · 2014-07-17 08:39:06Z

2

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

answered Jul 17, 2014 at 8:39

Noldor130884

1,0042 gold badges18 silver badges41 bronze badges

1 Comment

Samiko Over a year ago

Again, for others reference this does work but Rory's version makes more sense to me because of the way it is written.

Collectives™ on Stack Overflow

VBA Excel Macro Delete Sheets With String

3 Answers 3

Comments

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related