View Full Version : Removing named ranges
K. Georgiadis
10-06-2006, 01:43 PM
Is it possible to accomplish the following with macros:
Remove all named ranges in the workbook
Remove only those named ranges that contain a #REF! error (because referenced cells have been deleted)
mdmackillop
10-06-2006, 04:08 PM
1.
Sub DelAllRanges()
Dim r As Name
For Each r In ActiveWorkbook.Names
r.Delete
Next
End Sub
2.
Sub DelRefRanges()
Dim r As Name
For Each r In ActiveWorkbook.Names
If InStr(1, r, "#REF!") > 0 Then r.Delete
Next
End Sub
K. Georgiadis
10-06-2006, 04:29 PM
Fantastic! I have an immediate opportunity to test #1! Thanks.
Cyberdude
10-07-2006, 12:27 PM
Sub DelAllRanges()
For Each r In ActiveWorkbook.Names
r.Delete
Next
End Sub Malcolm, how would you Dim "r" in your sub?
mdmackillop
10-07-2006, 02:34 PM
Hi Sid,
It should be declared as Name. I've added it to the code.
Bob Phillips
10-08-2006, 02:56 AM
There are various system defined names which you should leave alone.
The ones that I know of are of the form
*_FilterDatabase
*Print_Area
*Print_Titles
*wvu.*
*wrn.*
*!Criteria
Best to test for these and not delete them.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.