VBA: Delete Hyperlinks
I’m not sure why most people dislike the automatic conversion of URL’s to Hyperlinks in Excel, in most cases it’s a good time saver for me.
However, If you don’t like the Automatic Hyperlink conversion, the code to remove Hyperlinks from a spreadsheet is rather simple. The following code deletes all of the Hyperlinks from Sheet1:
Sub DeleteHyperLinks() Sheet1.Hyperlinks.Delete End Sub
And if you don’t need a reusable Sub routine, rather a one time fix, use the Immediate Window:
1. Select the Sheet to remove Hyperlinks from
2. Press Alt+F11 to open the VBE
3. On the VBE Main Menu choose View->Immediate Window
4. In the Immediate Window paste the following code then hit Enter
ActiveSheet.Hyperlinks.Delete
Can't get the tutorial to work for you? Need help with your code?
Get answers right away at our AE Excel Support Forums!
Get answers right away at our AE Excel Support Forums!
If graphical object has hyperlink then it dosn’t delete.
what is the way to do it?
eg:- we have a organization chart and we placed a hyperlink in it.
How can we delete it?
Here’s one way:
ActiveSheet.Shapes(“AutoShape 1″).Select
Selection.ShapeRange.Item(1).Hyperlink.Delete
Sorry, but this code is not working…
Change “Autoshape 1″ with the codename of your autoshape.
Yet another way would be to use the looping code from this link: VBA Delete All Autoshapes, and the hyperlink.delete from my previous comment to remove all hyperlinks from all autoshapes.
I am trying somthing like this, its not removing hyperlink from org. chart
Dim objexcel As Excel.Application
Dim objworksheet As Excel.Worksheet
Dim objworkbook As Excel.Workbooks
Private Sub cmddelete_Click()
Set objexcel = CreateObject(“Excel.Application”)
objexcel.Workbooks.Open (“c:test hyper.xls”)
objexcel.WindowState = xlMinimized
objexcel.WindowState = xlMaximized
Dim Shp As Excel.ShapeRange
Dim IShp As Excel.Shape
Dim i As Integer
Dim j As Integer
On Error GoTo ResNextShp
For j = 1 To objexcel.ActiveSheet.Shapes.Count
objexcel.ActiveSheet.Shapes(j).Select
Set Shp = Selection.ShapeRange
If Shp.HasDiagramNode = msoTrue Then
For i = 1 To Shp.DiagramNode.Diagram.Nodes.Count
Set IShp = Shp.DiagramNode.Diagram.Nodes(i).Shape
If IShp.Hyperlink.Address “” Then
IShp.Hyperlink.Delete
End If
Next i
End If
Set IShp = objexcel.ActiveSheet.Shapes(j)
If IShp.Hyperlink.Address “” Then
IShp.Hyperlink.Delete
End If
Next j
i = 0
For i = objexcel.ActiveSheet.Hyperlinks.Count To 1 Step -1
objexcel.ActiveSheet.Hyperlinks(i).Delete
Next i
Exit Sub
ResNextShp:
Resume Next
End Sub
1. Code to unlink the hyperlink:
Sub RemoveHyperlinks()
Dim oField As Field
For Each oField In ActiveDocument.Fields
If oField.Type = wdFieldHyperlink Then
oField.Unlink
End If
Next
Set oField = Nothing
End Sub
so…. shape.hyperlink.delete syntax does work, however it fails if a hyperlink doesn’t exist on an object.
I can’t find anything off the top of my head to test whether there is a hyperlink on a shape or not (all logic fails with errors, bug?), so I present a very ugly solution:
Isolate the delete code by itself, try to delete for a shape, ignore any error. Not pretty, but works for me, Excel 2003
This code loops through all shapes on sheet1 and deletes the hyperlinks…
Sub DeleteAllShapeHyperlinks()
Sheet1.Activate
Dim GetShape As Shape
For Each GetShape In ActiveSheet.Shapes
KillHyperlink (GetShape.Name)
Next
End Sub
Function KillHyperlink(WhatShape As String)
On Error Resume Next
ActiveSheet.Shapes(WhatShape).Hyperlink.Delete
End Function
Sorry I can’t be more help,
Mark
dim theEnd as Integer ‘or Long
dim i as Integer ‘or Long
If (IsError(…hyperlink.delete) Then
‘nothing
Else
…hyperlink.delete
End If
‘or try
for i = 1 to theEnd
If (IsError(…hyperlink.delete) Then Goto nextOne
‘some code
…hyperlink.delete
nextOne:
‘code after deleting possibly a next if doing a loop
next i