Ironman

Well-known Member
Hi

I have hundreds of links to other sheets in the same workbook and I'm looking for a quick way of identifying broken links without me having to click on every single one.

I've searched the forum and I'm only able to find code for checking external hyperlinks.

Hope you can help?

Thank you!

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

Yongle

Well-known Member
Test this on a copy of your workbook
- place in a new Standard module and run
- new sheet added listing cells with value #REF!

Code:
``````Option Explicit

Sub BrokenInternal()
Optimise True
Dim Ws As Worksheet, Results As Worksheet, Cel As Range, Rng As Range, x As Long, F As String, A As String, N As String
x = 1
Results.Range("A1:C1") = Array("Sheet Name", "Cell", "Formula")
For Each Ws In ThisWorkbook.Worksheets
Set Rng = Ws.UsedRange
For Each Cel In Rng
If IsError(Cel) Then
If CVErr(Cel) = CVErr(2023) Then
x = x + 1
F = " " & Cel.Formula: A = Cel.Address(0, 0): N = Ws.Name
Results.Range("A" & x).Resize(, 3).Value = Array(N, A, F)
End If
End If
Next Cel
Next Ws
Results.Range("A:C").EntireColumn.AutoFit
Optimise False
End Sub

Private Sub Optimise(Tru As Boolean)
With Application
.Calculation = xlCalculationManual
.ScreenUpdating = Not Tru
If Tru = False Then .Calculation = xlCalculationAutomatic
End With
End Sub``````

Last edited:

Ironman

Well-known Member
My apologies for the delay - many thanks for your help!

Replies
0
Views
60
Replies
0
Views
107
Replies
1
Views
459
Replies
2
Views
190
Replies
0
Views
146

1,195,623
Messages
6,010,748
Members
441,567
Latest member
Flitbee

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back