Results 1 to 3 of 3
  1. #1
    Star Lounger
    Join Date
    Dec 2012
    Posts
    58
    Thanks
    6
    Thanked 0 Times in 0 Posts

    Can't delete the named range in VBA

    Hi All,

    I have a named range on a sheet called Info and then on multiple worksheets the named range SALE_NO is used in a VLOOKUP. I am trying to delete a named range called SALE_NO and then when the code runs it recreates it, I have created the code

    On Error Resume Next
    ThisWorkbook.Names("SALE_NO").Delete
    On Error GoTo 0

    Sheets("Info").Select
    [F18:G58].name = "SALE_NO"

    The problem I am facing is it looks like the user has somehow managed to create 10 instances of the named range but this is referring to ='C:\Users\dstimpson\AppData\Local\Microsoft\Windo ws\Temporary Internet Files etc which is not correct.

    When I review in Control F3 I can see it assigned to different sheets but refers to my local above directory

    There should only be 1 instance.

    When I try and delete SALE_NO using the above code it doesn’t delete them. Any ideas how to delete the named range in VBA?

    Thanks

  2. #2
    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
    Perhaps something like this

    Code:
    Dim nm As Name
    For Each nm In ThisWorkbook.Names
      If InStr(UCase(nm.Name), "SALE_NO") Then nm.Delete
    Next
    Steve

  3. The Following User Says Thank You to sdckapr For This Useful Post:

    stimpsond1 (2013-05-07)

  4. #3
    Star Lounger
    Join Date
    Dec 2012
    Posts
    58
    Thanks
    6
    Thanked 0 Times in 0 Posts
    Perfect Steve,

    I look at a number of varioations of this but missed it... Thanks alot.

    Regards,

Posting Permissions

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