CODEWITHSUNDEEP
vbaautomation
Justin Liu
Justin Liu

Reputation: 27

deleting a sheet causing automation error

Im writing a program to delete all the sheets with names of the months. However after the program delete a sheet it will give me an automation error

Sub DelSheet()

Dim i As Integer
Dim months() As String
months() = Split("January February March April May June July Auguest September October November December")

Dim ws As Worksheet

Application.DisplayAlerts = False


    For Each ws In Worksheets
        For i = 0 To UBound(months)

            If ws.Name = months(i) Then
                ws.Delete
            End If

        Next i
    Next ws

Application.DisplayAlerts = True



End Sub

Could it be that after I delete a sheet, when it begins the next loop the element in the Collection (worksheet) has changed.

For Each ws In Worksheets

I'm Lost

Upvotes: 0

Views: 796

Answers (2)

Tim Williams
Tim Williams

Reputation: 166456

Yes - when looping over a collection from which you might want to delete items, it's best to work backwards:

Sub DelSheet()

Dim i As Integer, n As Long, w as Long
Dim months() As String
months() = Split("January February March April May June July Auguest September October November December")

Dim ws As Worksheet

Application.DisplayAlerts = False

    n = Worksheets.Count
    For w = n to 1 Step - 1
        Set ws = Worksheets(w)
        For i = 0 To UBound(months)

            If  ws.Name = months(i) Then
                ws.Delete
                Exit For
            End If

        Next i
    Next w

Application.DisplayAlerts = True



End Sub

Upvotes: 1

user5412293
user5412293

Reputation:

Hi Here is another solution. In this case we use only one loop 

Sub DelSheet()

    Const strMONTHS As String = "January February March April May June July Auguest September October November December"

    Dim sh As Worksheet
    Dim arrSheetsToDelete() As String
    Dim i As Long

    ' Loop through the sheets and create the array
    For Each sh In ThisWorkbook.Sheets
        If Not InStr(1, strMONTHS, sh.Name) = 0 Then
            ReDim Preserve arrSheetsToDelete(i)
            arrSheetsToDelete(i) = sh.Name
            i = i + 1
        End If
    Next sh

    'Delete the array of sheets
    Application.DisplayAlerts = False
    ThisWorkbook.Sheets(arrSheetsToDelete).Delete
    Application.DisplayAlerts = True        

End Sub

Hope this helps :)

Upvotes: 1

Related Questions