Here is an example of a simple macro that solves a problem in Excel 2003 and earlier versions.
Range names can be corrupted if a cell that they refer to gets deleted. This doesn’t mean that the cell value gets deleted, but the cell itself is removed from the sheet.
If a range name refers to cell A1 and you delete the whole of column A then the name will display the #REF error because the cell it referred to no longer exists.
Excel 2007 and 2010 both allow you to delete multiple names at once. So you can open the Name Manager (Ctrl + F3) and then sort by Value by clicking the Value heading and then select all the names with a #REF value and click Delete. It’s a fairly easy process.
Not so in Excel 2003. It’s a boring, time consuming task. You can’t sort names by value and you can only delete names one at a time.
The macro below will remove all range names that have the #REF error in the blink of an eye. The macro checks if you want to continue before running. It works in all versions.
Sub DeleteAllNamesWithREFError()
'deletes all the names in the active workbook
'with a #REF error- confirms before running
Dim N As Name
If MsgBox("Are you sure?", vbYesNo + vbDefaultButton2, "Confirm macro") = vbNo _
Then Exit Sub
For Each N In ActiveWorkbook.Names
If InStr(N.Value, "#REF") Then N.Delete
Next N
End Sub
The attached file has the macro and some names with errors. Clicking the button will remove the names with errors.
Click here to see how you can run a Macro off a Quick Access Toolbar icon.
I want to remove/delete #REF from Line Charts. Could you pls provide me the solution.
Thanks,
Bhavik
Hi Bhavrik
Where are the #REF errors? – are they labels?
Regards
Neale
Hi Neale
Thanks for Quick Reply
In Legends I am getting this error.
Bhavik
Hi Bhavik
Can’t see how to do it. You can’t access the names of the legend entries via vba.
Sorry
Neale
Hi Neale
The scenario is as follows :
In sheet there is almost 10-15 charts. Each chart refer different data tables on the same sheet. When I delete a row from data table, there is a REF# in Legend of that chart. So I nee to remove this REF# entry from Legend of that chart.
Any workaround using macro ?
Regards,
Bhavik
Instead of deleting the row can’t you hide it? Hidden rows don’t display on the chart.
Hi Neale,
Totally Agree.
We have implemented this solution but not accepted. In Row Headings, it is very clearly visible lets say after 10th Row there is 12th Row & 11th Row is hidden.
Thanks
Bhavik
You can use grouping to show a row is hidden.
Select row 11 and hold Shift + Alt and press the right arrow – this adds an icon and makes it easy to hide and unhide,
I was just wondering, if checking for “#REF” in the value property using the instr function is the only way to check if a name has errors in it.
Is there a method where the instr function is not used?
You could go thru the value character by character but Instr is the most efficient way to do it.