VBA: Delete Hyperlinks

January 14th, 2005 | Categories: VBA | Tags:

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

deletehyperlinks

Leave a comment | Trackback
  1. vivek
    September 20th, 2005 at 06:54
    Reply | Quote | #1

    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?

  2. Mark
    September 20th, 2005 at 17:46
    Reply | Quote | #2

    Here’s one way:

    ActiveSheet.Shapes(“AutoShape 1″).Select
    Selection.ShapeRange.Item(1).Hyperlink.Delete

  3. vivek
    September 21st, 2005 at 03:02
    Reply | Quote | #3

    Sorry, but this code is not working…

  4. Mark
    September 21st, 2005 at 04:16
    Reply | Quote | #4

    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.

  5. vivek
    September 21st, 2005 at 23:00
    Reply | Quote | #5

    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

  6. Mark
    September 22nd, 2005 at 09:51
    Reply | Quote | #6

    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