asked on

remove all links from spreadsheet in Excel 2007

Hello,
Is there a way to remove all hyperlinks in an Excel 2007 worksheet or in a selected range of cells with just one step rather than having to do it one cell at a time?
Thanks

Jon von der Heyden

Hi Steve
See code below for macro solution. You need to add this to a standard module in your workbook (ALT + F11 > Insert > Module).
You are required to select the range first.

Option Explicit
 
Sub DelHyperLinks()
 
Dim hypCell As Hyperlink
 
For Each hypCell In Selection.Hyperlinks
    hypCell.Delete
Next hypCell
 
End Sub

Open in new window

Chris Bottomley

Hello Steve_Brady,

SImilar in concept:
The following macro will delete all hyperlinks on the current worksheet resetting the cell data as well if required.

Regards,
Chris

Sub noHyper()
Dim lnk As Hyperlink
Dim addr As Range 
    For Each lnk In ActiveSheet.Hyperlinks
        Set addr = ActiveSheet.Cells(lnk.Range.Row, lnk.Range.Column)
        lnk.Delete
        addr = ""
    Next
End Sub

Open in new window

ASKER CERTIFIED SOLUTION

Chris Bottomley

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION