Results 1 to 10 of 10
  1. #1
    Lounger
    Join Date
    Apr 2005
    Location
    Smyrna, Georgia, USA
    Posts
    31
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Delete Names Excel 97 (Excel 97)

    Is there a way to delete all Names from a spreadsheet (besides manually)

    Dee

  2. #2
    Platinum Lounger
    Join Date
    Jan 2001
    Posts
    3,788
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Delete Names Excel 97 (Excel 97)

    The easiest way is to use the very useful free add-in Name Manager by Jan Karel <!profile=Pieterse>Pieterse<!/profile>

  3. #3
    Lounger
    Join Date
    Apr 2005
    Location
    Smyrna, Georgia, USA
    Posts
    31
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Delete Names Excel 97 (Excel 97)

    This worked wonderfully! (Especially since there were 1500 names)

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Delete Names Excel 97 (Excel 97)

    I know you have it answered, but for those who want a means to do it without using an addin, you can just run this code:

    <pre>Sub DeleteAllNames()
    Dim Nm As Name
    For Each Nm In ActiveWorkbook.Names
    Nm.Delete
    Next
    End Sub</pre>


    Steve

  5. #5
    Lounger
    Join Date
    Apr 2005
    Location
    Smyrna, Georgia, USA
    Posts
    31
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Delete Names Excel 97 (Excel 97)

    Thanks Steve, If I had not been able to add in Names Manager, this would have worked well.

  6. #6
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Delete Names Excel 97 (Excel 97)

    Note that that will also delete all Excel's system names, like print area's and print titles, autofilter ranges,......

    But so will my Name Manager, unless you tell it to not show the system names first <g>.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Delete Names Excel 97 (Excel 97)

    Of course, you are correct:

    But the question was "Is there a way to delete all Names from a spreadsheet"

    You could, if desired, look for commonalities (underscore, perhaps) and not delete those names:

    <pre> For Each Nm In ActiveWorkbook.Names
    If InStr(Nm.Name,"_") = 0 then Nm.Delete
    Next</pre>


    Though, an underscore could come in ones created by us, and I do not know if all the "system names" have an underscore.

    Is there another (ie better) way to tell the "system names" (other than looking up in a list of them)?

    Steve

  8. #8
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Delete Names Excel 97 (Excel 97)

    It is just a matter of matching up with a couple I know of:

    *_FilterDatabase"
    "*Print_Area"
    "*Print_Titles"
    "*.wvu.*"
    "*wrn.*"
    "*!Criteria"
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  9. #9
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Delete Names Excel 97 (Excel 97)

    So something like:

    <pre>Sub DeleteNonSystemNames()
    Dim Nm As Name
    For Each Nm In ActiveWorkbook.Names
    If (InStr(Nm.Name,"_FilterDatabase") + _
    InStr(Nm.Name,"Print_Area") + _
    InStr(Nm.Name,"Print_Titles") + _
    InStr(Nm.Name,".wvu.") + _
    InStr(Nm.Name,".wrn.") + _
    InStr(Nm.Name,"!Criteria")) = 0 then Nm.Delete
    Next
    End Sub</pre>


    should do it.

    Steve

  10. #10
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Delete Names Excel 97 (Excel 97)

    Quite.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

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