Show cells with no dependents

[MBegg]

Hi all,


Does anyone have a nice trick to quickly find cells that don't have any dependents?


I'm building a model which is necessarily going to require trawling through financial reports to find the right data to enter. Obviously I want this task to involve the least pain possible, so want to make sure I'm not asking for any redundant data. I could go through each cell in turn and trace dependents, but just wondering if there's a quick way to highlight / find all of the cells in a sheet or range that aren't referenced by any calculations?

 

[Luke M]

Ouch. I sympathise in the amount of work you may have before you.

I think this thread may be of help:

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


A short macro like this might work. Highlights cells within the current selection that have no dependents in magenta.

[pre]

Sub Test()
Dim c As Range
Dim t_range As Range

For Each c In Selection
Set t_range = Nothing
On Error Resume Next
Set t_range = c.Dependents
On Error GoTo 0

'if there are no dependents then mark the cell in magenta
If t_range Is Nothing Then
c.Interior.ColorIndex = 7
End If
Next
End Sub

[/pre]
EDIT: Looks like this macro only works if dependents are on same sheet. Will continue to pursue other ideas...

 

[Luke M]

This looks more promising:

http://www.mrexcel.com/forum/excel-questions/371863-test-if-cell-has-dependents-precedents.html#post1851303

Might get a little slow, but modifying that code to highlight all cells on all worksheets with no dependents:

[pre]

Sub HighlightInputCells()
Dim wks As Worksheet
Dim rngFormulas As Range, rngCell As Range
Application.ScreenUpdating = False
For Each wks In ActiveWorkbook.Worksheets
On Error Resume Next
Set rngFormulas = Union(wks.UsedRange.SpecialCells(xlCellTypeBlanks), wks.UsedRange.SpecialCells(xlCellTypeConstants))
If Not rngFormulas Is Nothing Then
For Each rngCell In rngFormulas
If Not (HasDependents(rngCell)) Then rngCell.Interior.ColorIndex = 3
Next rngCell
Set rngFormulas = Nothing
End If
Next wks
Application.ScreenUpdating = True

End Sub
Function HasDependents(rngCheck As Range) As Boolean
Dim lngSheetCounter As Long, lngRefCounter As Long, rngDep As Range
On Error Resume Next
With rngCheck
.ShowDependents False
Set rngDep = .NavigateArrow(False, 1, 1)
If rngDep.Address(external:=True) = rngCheck.Address(external:=True) Then
HasDependents = False
Else
HasDependents = (Err.Number = 0)
End If
.ShowDependents True
End With
End Function

[/pre]

 

[MBegg]

Perfect! Unfortunately what that's shown me is that there are no fields I don't need.... Gonna be a fun one.

 

[Luke M]

Thanks for the feedback, sorry it didn't deliver better news. Let us know if there's more we can do to help out.

 

[MBegg]

Fancy going through a load of quarterly results pdfs and sending me a nicely formatted spreadsheet with all the data I want?