Microsoft Excel Functions & Formulas

Use a custom function to delete external links

in a worksheet

To distinguish between cells containing formulas and cells contain-

ing external links, all cells need to be checked. If a cell contains a

“[“ or “]”, it is a cell with a hyperlink to another workbook.

4

To delete all external links in a worksheet:

1. Press <Alt+F11>.

2. On the Insert menu, click Module.

3. Type the following macro:

Sub DeleteExLinks()

Dim rng As Range

With ActiveSheet

For Each rng In .UsedRange

If InStr(rng.Formula, "[") > 0 Then

rng.Value = rng.Value

End If

Next rng

End With

End Sub

Note: Starting this macro will delete all external links and

only values will be displayed.

Special Solutions ...