Excel Macro: Removing All the Hyperlinks from an Active Sheet

Sometimes I want to remove all the hyperlinks from a sheet.
So I’ve made this Excel VBA macro.
This macro allows us to remove all the hyperlinks from an active sheet.
Well, you might think the typical looping macro, but you don’t need it.

Macro Examples

Sub RemoveHyperlinks()

  ActiveSheet.Hyperlinks.Delete

End Sub

Description

We need only the following code:

  ActiveSheet.Hyperlinks.Delete

We don’t need looping. I will explain about this simple code.

ActiveSheet property

  ActiveSheet...

The Application.ActiveSheet property returns an active Worksheet object or a Chart object. So the Object Browser shows us that returned value is “Object”.

obj-Excel-Application-ActiveSheet

The ActiveSheet property is a member of the Excel.Global class, so we can omit the Application property.

obj-Excel-Global-ActiveSheet

Worksheet.Hyperlinks property

  ActiveSheet.Hyperlinks...

The Worksheet.Hyperlinks property (ActiveSheet.Hyperlinks) returns a Hyperlinks collection object that represents the hyperlinks on the worksheet.

obj-Worksheet-Hyperlinks

Hyperlinks.Delete method

  ActiveSheet.Hyperlinks.Delete

The Hyperlinks.Delete method deletes a Hyperlinks collection object.

Although the Hyperlinks is a collection object, but it has the Delete method.

obj-Hyperlinks-Delete

Actually, the Hyperlink object (a member of the Hyperlinks collection) also has the Delete method.

obj-Hyperlink-Delete

So we can remove all the hyperlinks by the For Each…Next statement, but we do not need the looping.

Sub RemoveHyperlinks()

  ActiveSheet.Hyperlinks.Delete

End Sub

Properties

http://www.relief.jp/itnote/archives/003401.php

Apply to

  • Excel 2013
  • Excel 2010
  • Excel 2007