|
|
|
|
|
|
|
|
|
Excel
|
Delete all named ranges in a workbook containing a #REF! error
|
|
|
Ease of Use
|
Easy
|
|
Version tested with
|
2003
|
|
Submitted by:
|
Ken Puls
|
|
Description:
|
This code will delete all named ranges in a workbook which contain a #REF! error
|
|
Discussion:
|
Code was created to deal with a workbook where a worksheet was deleted after it was no longer necessary. Unfortunately, there were several named ranges referring to this worksheet, and even more that referred to others. This code cycles through all names deleting any with REF! errors, but leaves the rest intact so as not to disrupt the functionality of the workbook. Please Note: If you have a formula on a spreadsheet which refers to a deleted name, it will return a #NAME error. The code makes no provision to fix this type of error.
|
|
Code:
|
instructions for use
|
Option Explicit
Sub DeleteRefErrRanges()
Dim nm As Name
For Each nm In ActiveWorkbook.Names
If InStr(1, nm.RefersTo, "#REF!") > 0 Then nm.Delete
Next nm
End Sub
|
|
How to use:
|
- Copy above code.
- In Excel press Alt + F11 to enter the VBE.
- Press Ctrl + R to show the Project Explorer.
- Right-click desired file on left (in bold).
- Choose Insert -> Module.
- Paste code into the right pane.
- Press Alt + Q to close the VBE.
- Save workbook before any other changes.
|
|
Test the code:
|
- Press Alt + F8 to display the Macro dialog box.
- Choose DeleteRefErrRanges and click Okay.
- Verify that all named ranges with REF! errors are gone.
|
|
Sample File:
|
ClearRefErrNames.zip 8.13KB
|
|
Approved by mdmackillop
|
|
This entry has been viewed 153 times.
|
|
|