find dependents (links) all at once

beancounter

Board Regular
Joined
Oct 30, 2002
Messages
111
I have a hugh worksheet with links going every direction. I have two columns where I picked up the cell and linked it over to another worksheet by accident. I am trying to find those cells. I did the GO TO and that didn't work and started to go through the cells one by one to see where they are going but that will take all day.

Is there a method to see all dependents for a group of cells?

Thanks,
John
 

Some videos you may like

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

craig.penny

Well-known Member
Joined
May 8, 2009
Messages
656
I have this code that's activated by a button on a userform. Each cell will show you its dependents unless you click "No" on the MsgBox. The arrows will still show all the cells with dependents, it's just that you won't see the address of those dependents after selecting "No". You could change this to be activated by a hotkey combination if you like.

Code:
Private Sub QuickComFour_Click()
Dim oCell As Range
Dim Pointer As Long
Dim tempString As String
Dim CheckString As String
Dim CutItOut  As VbMsgBoxResult
Application.ScreenUpdating = False
For Each oCell In Selection.Cells
      If oCell.Value <> "" Then
            Pointer = 0
            With oCell
                  .ShowDependents
                  On Error Resume Next
                  Do
                        Pointer = Pointer + 1
                        tempString = fullAddress(.NavigateArrow(False, 1, Pointer))
                        If Pointer = 1 Then
                              CheckString = tempString
                        Else
                              If CheckString = tempString Then Exit Do
                        End If
                        If CutItOut <> vbNo Then
                              CutItOut = MsgBox(fullAddress(.NavigateArrow(False, 1, Pointer)), vbYesNo)
                        End If
                        If err Then Exit Do
                  Loop Until err
            End With
            If Pointer > 2 Then
                  oCell.Interior.Color = 192
            End If
      End If
Next oCell
Application.ScreenUpdating = True
End Sub
 

beancounter

Board Regular
Joined
Oct 30, 2002
Messages
111
Craig,

Thank you - I guess you ran into a similiar situation. I will give it a go - possibly tomorrow when/if things slow down. I figured out where the dupes where but did it one by one until I found them. Luckly there were only three.

Thanks again,
John
 

craig.penny

Well-known Member
Joined
May 8, 2009
Messages
656
You're quite welcome!

As an aside this shows all precedents:

Code:
Private Sub QuickComThree_Click()
Dim oCell As Range
Dim Pointer As Long
Dim CutItOut  As VbMsgBoxResult
For Each oCell In Selection.Cells
      Pointer = 0
      With oCell
            .ShowPrecedents
            On Error Resume Next
            Do
                  Pointer = Pointer + 1
                  If Pointer > 10000 Then Exit Do
                  If CutItOut <> vbNo Then
                        CutItOut = MsgBox(fullAddress(.NavigateArrow(True, 1, Pointer)), vbYesNo)
                  End If
                  If err Then Exit Do
            Loop Until err
      End With
Next oCell
End Sub

And this is the UDF that I should have posted yesterday since that code wouldn't have worked without it:

Code:
Function fullAddress(inCell As Range) As String
      fullAddress = inCell.Parent.Name & "!" & inCell.Address
End Function

And last, this will remove all the arrows on a sheet:

Code:
ActiveSheet.ClearArrows

Cheers! :)
 

Watch MrExcel Video

Forum statistics

Threads
1,109,127
Messages
5,526,996
Members
409,733
Latest member
revender17

This Week's Hot Topics

Top