Page 1 of 2 12 LastLast
Results 1 to 10 of 14

Thread: Delete all defined named ranges from a selection

  1. #1
    Board Regular
    Join Date
    Aug 2019
    Posts
    188
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Delete all defined named ranges from a selection

    Is there a way to delete all named ranges from a selection? Here is the selection that I am trying to delete the named ranged from:
    Code:
    Range("CompOverviewTable[[#Headers],[Competitor_one]]").Select
        Range(Selection, Selection.End(xlToRight)).Offset(-1, 0).Select

  2. #2
    Board Regular
    Join Date
    Mar 2015
    Posts
    4,055
    Post Thanks / Like
    Mentioned
    73 Post(s)
    Tagged
    8 Thread(s)

    Default Re: Delete all defined named ranges from a selection

    Test in a copy of your workbook

    Code:
    Sub NamedRanges()
    'variables used
        Dim N As Name, Ws As Worksheet, Rng As Range, Msg As String, Refers As String
        
    'your code to select
        Range("CompOverviewTable[[#Headers],[Competitor_one]]").Select
        Range(Selection, Selection.End(xlToRight)).Offset(-1, 0).Select
    
    'delete named ranges which intersect with selection
        On Error Resume Next
        Set Ws = Selection.Parent
        For Each N In ThisWorkbook.Names
            Refers = N.RefersTo
            If Replace(Split(Refers, "!")(0), "=", "") = Ws.Name Then
                Set Rng = Ws.Range(Split(Refers, "!")(1))
                If Not Intersect(Rng, Selection) Is Nothing Then
                    Msg = Msg & vbCr & N.Name & vbTab & Refers
                    N.Delete
                End If
            End If
        Next
        If Not Msg = "" Then MsgBox Msg, vbOKCancel, "Deleted ...."
    End Sub
    Last edited by Yongle; Oct 15th, 2019 at 07:21 AM.

  3. #3
    Board Regular
    Join Date
    Aug 2019
    Posts
    188
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Delete all defined named ranges from a selection

    Thank you Yongle,

    The code ran without any errors but the named ranges did not get deleted

  4. #4
    Board Regular
    Join Date
    Aug 2019
    Posts
    188
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Delete all defined named ranges from a selection

    Quote Originally Posted by Yongle View Post
    Test in a copy of your workbook
    My previous reply is misleading. The code does not work

  5. #5
    Board Regular
    Join Date
    Mar 2015
    Posts
    4,055
    Post Thanks / Like
    Mentioned
    73 Post(s)
    Tagged
    8 Thread(s)

    Default Re: Delete all defined named ranges from a selection

    "The code does not work" is a very unhelpful comment on its own
    - if you want help you need to tell me where it is failing

    It works for me
    - did you run it from the sheet containing the table?
    Last edited by Yongle; Oct 15th, 2019 at 01:08 PM.

  6. #6
    Board Regular
    Join Date
    Aug 2019
    Posts
    188
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Delete all defined named ranges from a selection

    Sorry about that.

    Nothing much happens. I am running the code directly on the page at the moment but I would like it be able to run from a different sheet.

    When I run the code, the desired selection is selected but the defined names remain undeleted.

    I'm not sure if this makes a difference but the selection that I want all the defined names to delete from is not a table of itself. It is simply just a range of cells

  7. #7
    Board Regular
    Join Date
    Aug 2019
    Posts
    188
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Delete all defined named ranges from a selection

    Also,

    I figured out my range only needs to be:
    Code:
    Range("3:3").Select
    But I can adjust that in your code

  8. #8
    Board Regular
    Join Date
    Aug 2019
    Posts
    188
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Delete all defined named ranges from a selection

    Quote Originally Posted by Yongle View Post
    "The code does not work" is a very unhelpful comment on its own
    - if you want help you need to tell me where it is failing

    It works for me
    - did you run it from the sheet containing the table?
    Am I supposed to run this code somewhere specific? Because I have it assigned to an ActiveX Command Button

  9. #9
    Board Regular
    Join Date
    Mar 2015
    Posts
    4,055
    Post Thanks / Like
    Mentioned
    73 Post(s)
    Tagged
    8 Thread(s)

    Default Re: Delete all defined named ranges from a selection

    Perhaps I misunderstood what you want the code to do ..

    Your code below selects the ROW above CompOverviewTable starting in cell above header Competitor_one
    Code:
        Range("CompOverviewTable[[#Headers],[Competitor_one]]").Select
        Range(Selection, Selection.End(xlToRight)).Offset(-1, 0).Select
    What is in those cells
    - is it the NAMES of named ranges that should be deleted ?
    Last edited by Yongle; Oct 16th, 2019 at 11:08 AM.

  10. #10
    Board Regular
    Join Date
    Aug 2019
    Posts
    188
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Delete all defined named ranges from a selection

    Quote Originally Posted by Yongle View Post
    Perhaps I misunderstood what you want the code to do ..

    What is in those cells
    - is it the NAMES of named ranges that should be deleted ?
    The actual value in those cells are blank, but those cells have a defined name (range) that I created using the Name Manager. I would like to delete all the defined names in that selection so that all the defined names for those cells go back to the original value

    Example:
    From "Competitor_one" to "E1";
    From "Competitor_two" to "F2"

    After the defined names are deleted, they should no longer show up in Name Manager
    Last edited by gaudrco; Oct 16th, 2019 at 11:22 AM.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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