Try this:-

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Dim nName As Name
For Each nName In Names
If InStr(1, nName.RefersTo, "#REF!") 0 Then
nName.Delete
End If
Next nName
End Sub

Mike

"Maya" wrote:

Can anyone please let me know how to delete the named ranges of those
worksheets which have been deleted from the workbook.I have noticed that when
we delete the sheets, named ranges referening to those sheets donot get
deleted cos of this the size of the workbook gets increase .Is there any
event which will fire while deleting the sheet from the workbook.

thanksin advance for any of the suggestion.