×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Cells without dependent cells

Cells without dependent cells

Cells without dependent cells

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

RE: Cells without dependent cells

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

Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/

RE: Cells without dependent cells

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

RE: Cells without dependent cells

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.


CODE -->

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 

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources