Macro to delete a sheet if empty - error...

[jillian728]

We are using a macro that looks if ranges w/in sheets are blank and if yes -they are deleted. However, sometimes a user might delete the sheet b4 the macro is run. How can we tweak the macro so that if the sheet is found it is deleted and if not found, it doesn't delete it and doesn't cause an error.



If Application.Evaluate("=MAX(LEN('Sys Matrix'!D5:D62))") = 0 Then

Application.DisplayAlerts = False

Sheets("SYS Matrix").Delete

Application.DisplayAlerts = True

End If

If Application.Evaluate("=MAX(LEN('SW Mtx'!D5:D62))") = 0 Then

Application.DisplayAlerts = False

Sheets("SW Mtx").Delete

Application.DisplayAlerts = True

End If

 

[north19701]

Private Function SheetExists(sname) As Boolean
    Dim x As Object
    On Error Resume Next
    Set x = ActiveWorkbook.Sheets(sname)
    If Err = 0 Then SheetExists = True _
        Else SheetExists = False
End Function

You could add this function to the end of your macro and within your code place:

If SheetExists ("placeyoursheetnamehere") = true then

'place your delete code here

else

'what to do if the sheet doesn't exist

end if

I forget where I came across this function, but it's been quite useful.

 

[Andrew Poulsom]

? Put

On Error Resume Next

at the beginning of your code?

 

[Davers]

How about something like this....it will ignore your hidden sheets but will delete all others in the workbook whose range D5:D62 is blank...

Sub myEvalShts()

Dim mySht As Variant

Application.DisplayAlerts = False

For Each mySht In ActiveWorkbook.Sheets
    If mySht.Visible = True Then
        mySht.Activate
        If Application.Evaluate("=MAX(LEN(D5:D62))") = 0 Then
            mySht.Delete
        End If
    End If
Next
Application.DisplayAlerts = True

End Sub

 

[jillian728]

Thanks for all of these great suggestions. We will try them out when we return from holiday break and post the results!

 

[jillian728]

Thanks again everyone!

We used Andrew's suggestion and added the words On Error Resume Next to the beginning of the code and this worked perfectly!