current community

your communities

more stack exchange communities

Stack Exchange
tour help
stack overflow careers
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 3 down vote favorite

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
share|improve this question

3 Answers 3

up vote 8 down vote accepted

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.

share|improve this answer
3  
+ 1 Nicely Done –  Siddharth Rout Jul 17 '14 at 9:06
up vote 2 down vote

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
share|improve this answer
    
This does work so thank you but i ticked Rory's because it made more sense to me. –  Samiko Jul 17 '14 at 12:34
up vote 1 down vote

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
share|improve this answer
    
Again, for others reference this does work but Rory's version makes more sense to me because of the way it is written. –  Samiko Jul 17 '14 at 12:36

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.