• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1790
  • Last Modified:

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
0
samirst
Asked:
samirst
2 Solutions
 
chipconsultCommented:
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)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

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now