Removing multiple circular references

Tigerexcel

Active Member
Joined
Mar 6, 2020
Messages
493
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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.
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

YasserKhalil

Well-known Member
Joined
Jun 24, 2010
Messages
826
Try this code that will create a sheet (as report of all the cells that have the circular references)
VBA Code:
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

Active Member
Joined
Mar 6, 2020
Messages
493
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Very nifty Yasser! Is there a way to remove the content of these cells in bulk so that the circular reference is removed?
 

YasserKhalil

Well-known Member
Joined
Jun 24, 2010
Messages
826
Just refer to the cell that has the circular reference and use ClearContents method
VBA Code:
                        ws.Cells(vItem.Row, vItem.COLUMN).ClearContents
                        dRng.Offset(, 0).Value = ws.Name
 

Forum statistics

Threads
1,147,451
Messages
5,741,200
Members
423,648
Latest member
steel1968

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top