• Defined Names (Excel 2003)

    Author
    Topic
    #461791

    Is there a way to delete defined name list?

    Viewing 5 reply threads
    Author
    Replies
    • #1173005

      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.

      • #1173006

        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.

        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?

        • #1173007

          I thought excel use a common list of defined name.

          No – they are workbook specific. You would have to run the code on each workbook.

          • #1173012

            No – they are workbook specific. You would have to run the code on each workbook.

            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.

          • #1173167

            No – they are workbook specific. You would have to run the code on each workbook.

            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.

    • #1173169

      Try this:

      Code:
      Sub DeleteNames()
         Dim nm as Name
         For each nm in Activeworkbook.Names
      	  nm.Delete
         Next nm
      End Sub
      • #1173170

        Try this:

        Code:
        Sub DeleteNames()
           Dim nm as Name
           For each nm in Activeworkbook.Names
        	  nm.Delete
           Next nm
        End Sub

        It is not working properly. Keep some names un-deleted & a run time erron occured. On dubging, it highlights the “nm.Delete” syntex.

    • #1173171

      Sounds to me as though you may have some corrupt names in there. What names do you have left in the dialog?

      • #1173173

        Sounds to me as though you may have some corrupt names in there. What names do you have left in the dialog?

        You are absolutely right Rory. Most of them are corrupted as they reffered to un-identified locations/sheets and no more in use.

    • #1173175

      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.

      • #1173176

        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.

        Yes, perfect. Thank you very much Rory, for spare your precious time.

    • #1173177

      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?

    • #1173178

      Yes, you can download it from here (it’s a must-have!)

      • #1173181

        Yes, you can download it from here (it’s a must-have!)

        and one more

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

    Viewing 5 reply threads
    Reply To: Defined Names (Excel 2003)

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information: