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:

<pre>
Dim MyName As Name

For Each MyName In Application.Names
Range(MyName).Delete
Next
</pre>

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

<pre>
Dim MyName As Name

For Each MyName In Application.Names
MyName.Delete
Next

</pre>

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:

<pre>
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

</pre>


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]

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

<pre>
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
</pre>

HTH