Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

VBA Excel Macro Delete Sheets With String

Tags:

excel

vba

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
like image 323
Samiko Avatar asked Jul 17 '14 08:07

Samiko


People also ask

How do I delete a specific sheet in Excel VBA?

To delete a sheet using VBA, you need to use the VBA Delete method. You need to specify the sheet that you want to delete and then use this method. Let's say if you want to delete the “Sheet1”, then you need to mention sheet1 and then type a dot (.) and in the end, type “Delete”.


2 Answers

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.

like image 184
Rory Avatar answered Sep 21 '22 11:09

Rory


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
like image 25
Thundereagle Avatar answered Sep 22 '22 11:09

Thundereagle