problem in deleting the named ranges
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. |
problem in deleting the named ranges
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. |
All times are GMT +1. The time now is 05:39 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com