Where To Place Error Checking

[Ark68]

This code:

    For g = 0 To UBound(arr)
        
        With Worksheets(arr(g))
            Application.DisplayAlerts = False
            On Error Resume Next
            MsgBox (arr(g)) & " will be deleted"
            ThisWorkbook.Sheets(arr(g)).Delete
            On Error GoTo 0
            Application.DisplayAlerts = True
        End With
    
    Next g

Breaks with an error when it it is unable to find Worksheets(arr(g). Obviously, I have the error checking in the wrong position. I tried putting the error checking lines before and after the With/End With, it worked, but it still cycled through.

 

[RoryA]

Remove the With...End With as it isn't doing anything (apart from causing an error!). I would however also move the error handling simply because there is no point turning it off and on repeatedly

    Application.DisplayAlerts = False
    On Error Resume Next
    For g = 0 To UBound(arr)

        MsgBox (arr(g)) & " will be deleted"
        ThisWorkbook.Sheets(arr(g)).Delete

    Next g
    Application.DisplayAlerts = True

 

[Ark68]

Hi Rory ... I don't think I explained the problem well enough.

I tried your suggestion .... it works ... no errors. But it's running through the code even if there is no sheet to delete. It's not doing any harm, it's just flashing a lot of unnecessary messages.

 

[RoryA]

Try:

    Application.DisplayAlerts = False
For g = 0 To UBound(arr)
if not evaluate("isref('" & arr(g) & "'!a1)") then
MsgBox (arr(g)) & " will be deleted" 
       ThisWorkbook.Sheets(arr(g)).Delete
end if
Next g
    Application.DisplayAlerts = True

 

[Ark68]

Thanks Rory for your support. I definately wouldn't have been able to punch that code out!

Anyway, I've applied your suggestion. It still insists on running through the code as if the worksheet exists (warning message appears), but errors when it tries to delete a non-existant worksheet.

 

[RoryA]

I'm not sure why that fails but try:

dim oSheet as object
Application.DisplayAlerts = False
For g = 0 To UBound(arr)
on error resume next
set osheet = sheets(arr(g))
on error goto 0
if not oSheet is nothing then
MsgBox (arr(g)) & " will be deleted"
OSheet.Delete
set osheet = nothing
end if
Next g
    Application.DisplayAlerts = True

 

[Ark68]

Nice work! Thank you. Works wonderfully.

 

[Garry2Rs]

While the solution posted here is a good bandaid to the problem, the cause of the error still persists. This lies in how your array gets populated with the sheetnames, of which some of those sheetnames may no longer be valid. I'd be inclined to fix this!

 

[Ark68]

Hi Garry ... I'll have to be honest. With my limited knowledge of VBA ... I'm oblivious to obvious hints with those with greater knowledge LOL.

This is how my array is (simply) populated ...

 arr = Array("CUE", "CUL", "HPE", "HPL", "RPE", "RPL", "WPE", "WPL")

This deletion code I was seeking help on was to basically clean house of any residual worksheets previously created (these are temporary worksheets no longer needed in the workbook) by the application I was working on. That being said, I'm not sure what I need to improve on.

Cheers

 

[Garry2Rs]

Try...

Sub DeleteTempSheets()
  Dim wks As Worksheet
  Const sTempNames$ = "CUE,CUL,HPE,HPL,RPE,RPL,WPE,WPL"
  Application.DisplayAlerts = False
  For Each wks In ActiveWorkbook.Worksheets
    If InStr(1, sTempNames, wks.Name) > 0 Then wks.Delete
  Next 'wks
  Application.DisplayAlerts = True
End Sub

..which will not need error handling because it loops existing sheets!