VBA Code to delete all Named Ranges in a worksheet?

[Chris The Rock]

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?

[Mark O'Brien]

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

[Chris The Rock]

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.

[Chris The Rock]

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!

[Mark O'Brien]

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

[Chris The Rock]

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

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

[Mark O'Brien]

Damn, 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