Named ranges with #REF! error occur when there is a 'reference' error. This usually happens when the cell or range itself is removed. For example, if there is a named range that refers to cells in Sheet1!B3 and you delete an entire row, column, or worksheet that contains this named range, you will get a #REF! error. In this article, we're going to show you how to delete named ranges with #REF Excel errors using VBA.
With #REF! errors, named ranges become useless unless you set their references again manually. However, you can also delete them easily using VBA. We can check all names in a workbook using a For Each…Next loop. If the value contains a #REF! error then we can delete it.
Firstly, you need to add the module into the workbook or the add-in file. Copy and paste the code into the module to run it. The main advantage of the module method is that it allows saving the code in the file, so that it can be used again later. Furthermore, the subroutines in modules can be used by icons in the menu ribbons or keyboard shortcuts. Remember to save your file in either XLSM or XLAM format to save your VBA code. Below is a sample code.
Sub DeleteNamedRangesWithREF() Dim nm As Name For Each nm In ActiveWorkbook.Names If InStr(nm.Value, "#REF!") > 0 Then nm.Delete End If Next nm End Sub
