Results 1 to 13 of 13
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Baltimore, MD, Maryland, USA
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Cleanup of Named Ranges (Office97 SR2)

    Ooops!! I just realized that these 6 WorkBook applications that I created are not going to work correctly because some of the variables that I am obtaining data for (by querying an ACCESS database), are not unique. Its like the Variable name and the Title fields have to be concatenated to creat the unique field for the applications. OK, thats pretty easy BUT I have already created about 200 to 250 named ranges with the unconcatenated variable name. Then, when I did the concatenation in the database query, I used a "-" separator. Then, after running my program to recreate the named ranges, I realized that XLconverted all the "-"s to "_" so my range names no longer agree with the cells in which the names appear. So, now I have to do the queries with "_"s.
    Anyway, I now have several hundred named ranges in one of the WorkBook applications (the one I was trying to fix) that I need to delete. Is there anyway that I can use VBA to delete them?? Otherwize, I have to do it one by one or recreate the entire application.
    Anyone bale me out of this swamp --- pleaseee. <img src=/S/crybaby.gif border=0 alt=crybaby width=15 height=15>

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Cleanup of Named Ranges (Office97 SR2)

    If you want to delete all range names try :<pre> Dim rngName As Name
    For Each rngName In ThisWorkbook.Names
    rngName.Delete
    Next</pre>

    Andrew C

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Baltimore, MD, Maryland, USA
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cleanup of Named Ranges (Office97 SR2)

    Hi Andrew:
    Is there anyway I could specify those that I want to delete by, say, the first three letters of the name?

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cleanup of Named Ranges (Office97 SR2)

    Sure:

    <pre>Dim rngName As Name
    For Each rngName In ThisWorkbook.Names
    If Left(rngName.Name, 3) = "xxx" Then
    rngName.Delete
    End If
    Next
    </pre>

    Legare Coleman

  5. #5
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Cleanup of Named Ranges (Office97 SR2)

    I don't work much with the Names collection, and I'm trying to delete specific range names out of a larger group of names; I have been working on a more succinct way to do this:

    With Worksheets("Summary")
    .Names("Summary!RangeName1").Delete
    .Names("Summary!RangeName2").Delete
    .Names("Summary!RangeName3").Delete
    .Names("Summary!RangeName4").Delete
    End With

    But I can't get anything going with items in an array or a for next loop ... any suggestions?
    -John ... I float in liquid gardens
    UTC -7ąDS

  6. #6
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Cleanup of Named Ranges (Office97 SR2)

    I would use Jan Karel's name manager to generate a list of all of your names in a new sheet, then edit that sheet deleting all of the names that you want to keep, then use the macro below to delete all of the names on the list. HTH --Sam
    <pre>Option Explicit

    Sub Macro1()
    Dim i As Integer
    With Sheets("Names 2003-Feb-07")
    For i = 2 To .UsedRange.Rows.Count
    ActiveWorkbook.Names(.Cells(i, 1).Value).Delete
    Next i
    End With
    End Sub</pre>

    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  7. #7
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Cleanup of Named Ranges (Office97 SR2)

    I should have been more explicit. This something I need to do over and over via VBA.

    From a global pricing template a customer-specific rate is created. Since the pricing details may be changed and the summary reissued, this is done by VBA copying an interim summary to a distributable summary, and turning certain formulas to values on the distributable summary. The range names are those cell ranges in which formulas get turned into values; those range names are copied from the interim summary. For not very critical reasons I'd prefer to delete those four range names from summary sheet which gets distributed.

    The code posted does what I need, I'm just looking for a less redundant way to do it.
    -John ... I float in liquid gardens
    UTC -7ąDS

  8. #8
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Cleanup of Named Ranges (Office97 SR2)

    Using the KISS rule, I pefer your code, but if you want a loop, you get a loop. Six lines instead of 6: <img src=/S/wink.gif border=0 alt=wink width=15 height=15>
    <pre>Dim i As Integer
    With Worksheets("Summary")
    For i = 1 To 4
    .Names("Summary!RangeName" & i).Delete
    Next i
    End With</pre>

    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  9. #9
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Cleanup of Named Ranges (Office97 SR2)

    > not trying to mess with you
    Do you expect me to believe that? <img src=/S/notmyfault.gif border=0 alt=notmyfault width=15 height=15> At least now I understand the question! Is this what you want?
    <pre>With Worksheets("Summary")
    For Each v In Array("ABCF", "ABCH", "WX", "PQR")
    .Names("Summary!Set" & v & "Cells").Delete
    Next v
    End With</pre>

    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  10. #10
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Cleanup of Named Ranges (Office97 SR2)

    <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23> Good work Sammy. You get a gold star and you can leave early. I tried something like that but between my ignorance of syntax and of the Names collection I couldn't get it right.
    -John ... I float in liquid gardens
    UTC -7ąDS

  11. #11
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Cleanup of Named Ranges (Office97 SR2)

    I'm not trying to mess with you or annoy you <img src=/S/grin.gif border=0 alt=grin width=15 height=15>, but I guess I'm succeeding. Since there's already a "With Worksheets("summary") for some other stuff, it's 4 incremental lines, and the names in my example were hypothetical, they are more like (still slightly censored):

    "Summary!SetABCFCells"
    "Summary!SetABCHCells"
    "Summary!SetWXCells"
    "Summary!SetPQRCells"

    -- the range names have to do with the groups of products they describe. Guess I could change 'em to xxx1, xxx2, etc. But I think I'll go away and quit bothering you.
    -John ... I float in liquid gardens
    UTC -7ąDS

  12. #12
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Cleanup of Named Ranges (Office97 SR2)

    Thanks! I got it! <IMG SRC=http://www.mtl.com/sam/images/star.gif> Unfortunately, you have to use a variant as the loop variable. -Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

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

    Re: Cleanup of Named Ranges (Office97 SR2)

    Seeing you got your solution, I would still advise you to try my Name Manager (from the Excel MVP page below). With hundreds of named ranges it will be a tremendous help when you need to audit or change anything.
    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
  •