#REF location ? POssible

[zone709]

HI i have on sheet1 the whole sheet full of alot of stuff. Going past 5000 lines. Is there a formula i would prefer or even a macro I can put on sheet2 to locate on sheet1 where any cells that have #Ref . It takes forever to find thanks

 

[Scott Huish]

Sub FindRefErrors()
Dim c As Range
Worksheets(2).Range("A:A").ClearContents
For Each c In Worksheets(1).UsedRange
If Application.WorksheetFunction.IsError(c) Then
    If c.Text = "#REF!" Then Worksheets(2).Range("A" & Rows.Count).End(xlUp).Offset(1) = c.Address
End If
Next
End Sub

 

[zone709]

Works thanks so much. Much better