VBA Code to delete all Named Ranges in a worksheet?
VBA Code to delete all Named Ranges in a worksheet?
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 7 of 7

Thread: VBA Code to delete all Named Ranges in a worksheet?

  1. #1
    Board Regular Chris The Rock's Avatar
    Join Date
    Feb 2002
    Location
    Longmont, CO
    Posts
    285
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    Is there a simple line of code that can remove all named ranges in a worksheet? How about all the named ranges in an entire workbook?

  2. #2
    MrExcel MVP Mark O'Brien's Avatar
    Join Date
    Feb 2002
    Location
    Columbus, OH, USA
    Posts
    3,530
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Not quite sure what you mean, if you mean remove the data from the named ranges use this:


    Dim MyName As Name

    For Each MyName In Application.Names
    Range(MyName).Delete
    Next


    If you mean to remove the names from the ranges, use this:


    Dim MyName As Name

    For Each MyName In Application.Names
    MyName.Delete
    Next



    These will work for the whole workbook, not individual sheets

    HTH


  3. #3
    Board Regular Chris The Rock's Avatar
    Join Date
    Feb 2002
    Location
    Longmont, CO
    Posts
    285
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I will give this a try, thanks.

    What I am specifically referring to is the "Names" that you can define using Insert/Name/Define - each of these refers to a group of cells.

    I refresh data daily from an external source, and what's been happening is that during the course of the month, each time I've refreshed, it's created a new Named Range. I simply want to remove these Names from the worksheets.

  4. #4
    Board Regular Chris The Rock's Avatar
    Join Date
    Feb 2002
    Location
    Longmont, CO
    Posts
    285
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I tried this, (Example #2) and it does everything I want it to...but it also destroys my print areas. Is there any way to get this to work without blowing away the Print Areas?

    Thanks!

  5. #5
    MrExcel MVP Mark O'Brien's Avatar
    Join Date
    Feb 2002
    Location
    Columbus, OH, USA
    Posts
    3,530
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Nope, there's no way to preserve your print setup.

    However, you can try this code. It creates an array and stores your printarea for each page, deletes the names, then resets all of the print areas. Give it a try because I haven't really tested it:


    Dim MyPrintArea() As String
    Dim i As Integer
    Dim MyName As Name

    ReDim MyPrintArea(Sheets.Count)

    For i = 1 To Sheets.Count
    MyPrintArea(i) = Sheets(i).PageSetup.PrintArea
    Next


    For Each MyName In Application.Names
    MyName.Delete
    Next

    For i = 1 To Sheets.Count
    Sheets(i).PageSetup.PrintArea = MyPrintArea(i)
    Next




    HTH

  6. #6
    Board Regular Chris The Rock's Avatar
    Join Date
    Feb 2002
    Location
    Longmont, CO
    Posts
    285
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    MyPrintArea(i) = Sheets(i).PageSetup.PrintArea

    THis particular line of code gives me a Run Time Error 13 'Type Mismatch'.

  7. #7
    MrExcel MVP Mark O'Brien's Avatar
    Join Date
    Feb 2002
    Location
    Columbus, OH, USA
    Posts
    3,530
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    ****, you've probably got Charts inserted there as well. If that's the case, use this code:


    Dim MyPrintArea() As String
    Dim i As Integer
    Dim MyName As Name

    ReDim MyPrintArea(Worksheets.Count)

    For i = 1 To Worksheets.Count
    MyPrintArea(i) = Worksheets(i).PageSetup.PrintArea
    Next


    For Each MyName In Application.Names
    MyName.Delete
    Next

    For i = 1 To Worksheets.Count
    Worksheets(i).PageSetup.PrintArea = MyPrintArea(i)
    Next


    HTH

User Tag List

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
  •  

 

 
DMCA.com