|
|
|
|
|
|
|
|
|
Excel
|
Delete Hyperlinks, Links, Formulas From Worksheet
|
|
|
Ease of Use
|
Easy
|
|
Version tested with
|
2000
|
|
Submitted by:
|
johnske
|
|
Description:
|
On running the code, all hyperlinks, internal and external links, and formulae are deleted from the worksheet, but all values are retained.
|
|
Discussion:
|
You may have used all sorts of links and formulas to collate data on a worksheet but, once collated, all these links etc. are no longer required. Alternatively, you may wish to forward the worksheet with the collected data to someone but you don't want them to have access to your formulae or to have them getting that annoying "Update Links?" message every time they open the workbook. Should you also wish to remove all VBA code, see this entry http://www.vbaexpress.com/kb/getarticle.php?kb_id=93
|
|
Code:
|
instructions for use
|
Option Explicit
Sub DeleteHyperlinks()
Dim Query As VbMsgBoxResult
Query = MsgBox("CAUTION: Every single link, formula " & _
"& hyperlink on this " & vbLf & _
"sheet will be deleted - Do you " & _
"still want to proceed?", vbYesNo, _
"Sever All Links?")
If Query = vbNo Then Exit Sub
With Cells
.Select
.Copy
Selection.PasteSpecial Paste:=xlValues
Application.CutCopyMode = False
End With
Cells.Hyperlinks.Delete
[A1].Select
End Sub
|
|
How to use:
|
- Open an Excel workbook
- Select Tools/Macro/Visual Basic Editor
- In the VBE window, select Insert/Module
- Copy and paste the code into the Module
- Now select File/Close and Return To Microsoft Excel
- Save your work
|
|
Test the code:
|
- Select Tools/Macro/Macros.../DeleteHyperlinks/Run
- Select the various parts that were linked to see that all links are severed
- To apply this to the whole book, you need to look at the allied knowledge-base entry http://www.vbaexpress.com/kb/getarticle.php?kb_id=378
|
|
Sample File:
|
DeleteLinks_Sheet.zip 8.84KB
|
|
Approved by mdmackillop
|
|
This entry has been viewed 164 times.
|
|
|