find dependents (links) all at once

[beancounter]

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

 

[craig.penny]

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.

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]

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]

You're quite welcome!

As an aside this shows all precedents:

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:

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:

ActiveSheet.ClearArrows

Cheers!

 

[craig.penny]

Hi John,

Did you give this a go?

Craig