Macro for removing hyperlinks from spreadsheet

Hi Experts,
I have an Excel spreadsheet that has hyperlinks randomly scattered in different cells within the spreadsheet, is there a way of having a macro that can remove all hyperlinks in the spreadsheet. Thanks
Sam
samirstAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
Advertise Here
 
chipconsultConnect With a Mentor Commented:
This will delete the hyperlink but the text will remain.

Sub test()
For Each hp In ActiveSheet.Hyperlinks
 hp.Delete
Next hp

End Sub

Claus Henriksen
0
 
zorvek (Kevin Jones)Connect With a Mentor ConsultantCommented:
Public Sub RemoveHyperlinks()

   Dim Hyperlink As Hyperlink
   Dim Count As Long
   
   For Each Hyperlink In ActiveSheet.Hyperlinks
      Hyperlink.Delete
      Count = Count + 1
   Next Hyperlink
   
   MsgBox Count & " hyperlinks deleted."

End Sub

Kevin
0
 
samirstAuthor Commented:
Guys, both solutions worked perfectly and have the exact timestamp when posted. So the best option is to split the points equally. Many thanks for your help.
Sam
0
 
Rory ArchibaldCommented:
Sub RemoveHyperlinks()
ActiveSheet.Hyperlinks.Delete
End Sub

will also do it.
Regards,
Rory
0
 
chipconsultCommented:
A little add-on:
If you want to delete the text as well it is done with:

Sub RemoveHyperlinks()

For Each hl In ActiveSheet.Hyperlinks

    Range(hl.Range.Address).ClearContents

Next hl

End Sub

Claus Henriksen
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.