Cells without dependent cells

[AELLC]

Is there any way within Excel, that on a given worksheet, it will show which cells (that have a formula) have no dependent cells?

 

[IDS]

Does "Trace Dependents" on the Formulas Tab do what you want?

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[IRstuff]

Not easily. You could wrap "Selection.DirectPrecedents.Select" with a loop and error trapping and run through every non-empty cell.

TTFN
faq731-376

Need help writing a question or understanding a reply? forum1529

 

[MintJulep]

I played around with Range.DirectDependents a bit - without success. But perhaps I was trying to be too clever with it.

 

[Denial]

Take a look at URL

http://www.ozgrid.com/forum/showthread.php?t=73194

but note that I have not tried to test this code.

I found this by using a site I think I discovered through Eng-Tips:

http://www.jpsoftwaretech.com/vba-search-engine/

 

[MintJulep]

This seems to work, subject to the published limitations of Range.ActiveDependents

Note also that if it is used ON THE SHEET then supplying a range as the argument CREATES a dependent cell or cells.

Public Function DependOnMe(Target As Range)
Dim TestCell As Range
Dim DependCount As Integer
On Error GoTo ErrorHandle
For Each TestCell In Target
If Not IsEmpty(TestCell.Address) Then
DependCount = TestCell.DirectDependents.Count
Debug.Print TestCell.Address & " has " & DependCount & " dependent cells."
End If
Next
End
ErrorHandle:
If Err.Number = 1004 Then DependCount = 0
Debug.Print TestCell.Address & " has " & DependCount & " dependent cells."
Resume Next
End Function