Results 1 to 15 of 15
  1. #1
    3 Star Lounger
    Join Date
    May 2008
    Location
    India
    Posts
    306
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Is there a way to delete defined name list?

    [attachment=85060:untitled.JPG]
    Attached Images Attached Images
    Regards
    Prasad

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    If you have Jan Karel Pieterse's Name Manager add-in, you can select and delete them all in one go. Otherwise you would need code:
    Code:
    Sub DeleteNames()
    
    Application.ExecuteExcel4Macro "SUM(DELETE.NAME(NAMES()))"
    End Sub
    for example.
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    3 Star Lounger
    Join Date
    May 2008
    Location
    India
    Posts
    306
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='rory' post='788415' date='11-Aug-2009 18:11']If you have Jan Karel Pieterse's Name Manager add-in, you can select and delete them all in one go. Otherwise you would need code:
    Code:
    Sub DeleteNames()
    
    Application.ExecuteExcel4Macro "SUM(DELETE.NAME(NAMES()))"
    End Sub
    for example.[/quote]
    I thought excel use a common list of defined name. I have deleted the list from one wb with help of code provided, but it is still there in another wbs. what to do?
    Regards
    Prasad

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    [quote name='prasad' post='788416' date='11-Aug-2009 14:02']I thought excel use a common list of defined name.[/quote]

    No - they are workbook specific. You would have to run the code on each workbook.
    Regards,
    Rory

    Microsoft MVP - Excel

  5. #5
    3 Star Lounger
    Join Date
    May 2008
    Location
    India
    Posts
    306
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='rory' post='788418' date='11-Aug-2009 18:40']No - they are workbook specific. You would have to run the code on each workbook.[/quote]
    That way, it is better to keep them alive. It will take me a day to delete them from each workbook. Thanks Rory for help.
    Regards
    Prasad

  6. #6
    3 Star Lounger
    Join Date
    May 2008
    Location
    India
    Posts
    306
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='rory' post='788418' date='11-Aug-2009 18:40']No - they are workbook specific. You would have to run the code on each workbook.[/quote]
    Is it, any how, possible to delete the defined name list from entire workbook having more than 1 sheet? The code provided delete list from selected sheet only.
    Regards
    Prasad

  7. #7
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Try this:
    Code:
    Sub DeleteNames()
       Dim nm as Name
       For each nm in Activeworkbook.Names
    	  nm.Delete
       Next nm
    End Sub
    Regards,
    Rory

    Microsoft MVP - Excel

  8. #8
    3 Star Lounger
    Join Date
    May 2008
    Location
    India
    Posts
    306
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='rory' post='788589' date='12-Aug-2009 17:59']Try this:
    Code:
    Sub DeleteNames()
       Dim nm as Name
       For each nm in Activeworkbook.Names
    	  nm.Delete
       Next nm
    End Sub
    [/quote]
    It is not working properly. Keep some names un-deleted & a run time erron occured. On dubging, it highlights the "nm.Delete" syntex.
    Regards
    Prasad

  9. #9
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Sounds to me as though you may have some corrupt names in there. What names do you have left in the dialog?
    Regards,
    Rory

    Microsoft MVP - Excel

  10. #10
    3 Star Lounger
    Join Date
    May 2008
    Location
    India
    Posts
    306
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='rory' post='788592' date='12-Aug-2009 18:16']Sounds to me as though you may have some corrupt names in there. What names do you have left in the dialog?[/quote]
    You are absolutely right Rory. Most of them are corrupted as they reffered to un-identified locations/sheets and no more in use.

    [attachment=85078:untitled.JPG]
    Attached Images Attached Images
    Regards
    Prasad

  11. #11
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    n5 is not a valid range name as it's a cell reference. I suggest you switch to R1C1 style referencing via the Tools-Options dialog, then run the code gain, then switch back to A1-style.
    Regards,
    Rory

    Microsoft MVP - Excel

  12. #12
    3 Star Lounger
    Join Date
    May 2008
    Location
    India
    Posts
    306
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='rory' post='788596' date='12-Aug-2009 18:29']n5 is not a valid range name as it's a cell reference. I suggest you switch to R1C1 style referencing via the Tools-Options dialog, then run the code gain, then switch back to A1-style.[/quote]
    Yes, perfect. Thank you very much Rory, for spare your precious time.
    Regards
    Prasad

  13. #13
    3 Star Lounger
    Join Date
    May 2008
    Location
    India
    Posts
    306
    Thanks
    0
    Thanked 0 Times in 0 Posts
    If you have Jan Karel Pieterse's Name Manager add-in, you can select and delete them all in one go
    BTW, can I have the Name Manager add-in?
    Regards
    Prasad

  14. #14
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Yes, you can download it from here (it's a must-have!)
    Regards,
    Rory

    Microsoft MVP - Excel

  15. #15
    3 Star Lounger
    Join Date
    May 2008
    Location
    India
    Posts
    306
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='rory' post='788601' date='12-Aug-2009 18:46']Yes, you can download it from here (it's a must-have!)[/quote]


    and one more


    I have to go home and drinking with driving is not permited here.
    Regards
    Prasad

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •