Find errors in dropdown lists

[tiredExcelUser]

I have a model with a lot of dropdown lists that refer to a validation list in the same sheet. sometimes a user will accidentally delete the list that the dropdown is referring to. this results in a #REF! error in the data validation source for the dropdown but that doesn't show up in the error check. does anyone know how to search the file to see if any of the dropdowns have an error in their sources?

 

[James006]

Hi,

You can adapt the following

Sub LoopNamedRangesWithREF()
    Dim nm As Name
    For Each nm In ActiveWorkbook.Names
        If InStr(nm.Value, "#REF!") > 0 Then
           ' To Delete
            'nm.Delete
        End If
    Next nm
End Sub