Code Find Cells Without Dependents

[adrian.stock]

Hi,

I am trying to write some code to run through a workbook and identify dead ends i.e. cells with no dependents. I can isolate the cells I need to check and tried to count dependent cells to see which had none. My code to do this is:

If Cell.Dependents.Count < 1 Then

This creates "runtime error 1004: no cells found" when a cell with no dependents is found. I have tried using ISERROR and ISNUMBER and a few other things to either trap this error or turn it into something useful. I cannot do this and would be pleased to get some help.

It seems that whatever is returned from my expression is not an error code or a null or a number.

Thanks in advance
Adrian

[Wigi]

Re: Code Find Cells Without Dependents

Hi

welcome to the nice Ozgrid forum

Here's code I wrote to do it:

Sub nodependents()
    
    Dim r As Range
    Dim dep As Range
    Dim rNoDependents As Range
    Dim RangeToEvaluate As Range
    
    With ActiveSheet.UsedRange
        On Error Resume Next
        Set RangeToEvaluate = .SpecialCells(xlCellTypeFormulas)
        Set RangeToEvaluate = Application.Union(RangeToEvaluate, .SpecialCells(xlCellTypeConstants))
        On Error GoTo 0
    End With
    
    If Not RangeToEvaluate Is Nothing Then
    
        For Each r In RangeToEvaluate
            
            On Error Resume Next
            Set dep = r.Dependents
            On Error GoTo 0
            
            'no dependents found?
            If dep Is Nothing Then
                If rNoDependents Is Nothing Then
                    Set rNoDependents = r
                Else
                    Set rNoDependents = Application.Union(r, rNoDependents)
                End If
            End If
        Next
        
    End If
    
    'output result
    If Not rNoDependents Is Nothing Then MsgBox rNoDependents.Address Else MsgBox "no cells without dependents"
    
End Sub

Wigi

[adrian.stock]

Re: Code Find Cells Without Dependents

Thank you, that helps a lot, I have now sorted it.

[Wigi]

Re: Code Find Cells Without Dependents

You're very welcome Adrian.