Removing multiple circular references

[Tigerexcel]

I have a file with multiple "time stamp" entries which all produce circular references. The file is extremely slow to work with even with the settings on Manual Calculation.
An example of these formula in these references is =IF(K3<>"",IF(P3="",NOW(),P3),"") in cell P3. There are hundreds of these in a worksheet and there are at least 100 worksheets. Is there a way that I can remove all circular references at the same time. It is ok if the contents of cells with the circular reference are deleted. Thinking some sort of macro but am open to any suggestions.

 

[YasserKhalil]

Try this code that will create a sheet (as report of all the cells that have the circular references)

Sub Find_Circular_References_In_All_Worksheets()
    Dim vItem, result, ws As Worksheet, sh As Worksheet, dRng As Range
    Set sh = Sheets.Add(After:=Sheets(Sheets.Count))
    Set dRng = sh.Range("A1")
    sh.DisplayRightToLeft = False
    For Each ws In ThisWorkbook.Worksheets
        On Error GoTo errHandler
        If ws.Name <> sh.Name Then
            With ws
                For Each vItem In .UsedRange
                    If Left(vItem.Formula, 1) = "=" Then
                        result = Intersect(.Range(vItem.Address), .Range(vItem.Precedents.Address))
                        dRng.Offset(, 0).Value = ws.Name
                        dRng.Offset(, 1).Value = vItem.Address(False, False)
                        dRng.Offset(, 2).Value = "'" & vItem.Formula
                        Set dRng = dRng.Offset(1)
                    End If
Skipper:
                Next vItem
            End With
        End If
    Next ws
    sh.Columns.AutoFit
    Exit Sub
errHandler:
    Resume Skipper
End Sub

 

[Tigerexcel]

Very nifty Yasser! Is there a way to remove the content of these cells in bulk so that the circular reference is removed?

 

[YasserKhalil]

Just refer to the cell that has the circular reference and use ClearContents method

                        ws.Cells(vItem.Row, vItem.COLUMN).ClearContents
                        dRng.Offset(, 0).Value = ws.Name