Excel, just like other Office products, can detect URLs and email addresses, and turn them automatically into hyperlinks. While this can usually speed things up when creating workbooks, they can become nuisance when you don't want them to be clickable links with that very obvious blue underlined formatting. In this article, we're going to show you how to remove hyperlink in Excel quickly using VBA.

 

How to remove hyperlink in Excel

A Hyperlink is a range object that is collected in the Hyperlinks collection. This means that every range has a Hyperlinks collection that is bound to that range. After calling a Hyperlinks collection under a range, you can remove them easily with a Delete command.

Range("A1").Hyperlinks.Delete

All you need to do is determining the range. You can use the Selection object for selected cells or ranges, and ActiveSheet.UsedRange for the entire worksheet. You can find the codes for these operations below.

You can use codes in two ways:

• Module
• Immediate Window

In the Module method, you need to add the module into the workbook or the add-in file. Copy and paste the code into the module to run it. The main advantage of the module method is that it allows saving the code in the file, so that it can be used again later. Furthermore, the subroutines in modules can be used by icons in the menu ribbons or keyboard shortcuts. Remember to save your file in either XLSM or XLAM format to save your VBA code.

The Immediate Window method, on the other hand, is essentially a quick and dirty method where you can simply copy and paste the code into the Immediate Window and press the Enter key to run it. Unfortunately, any code you use in the Immediate Window will not be saved. Also note that icons and keyboard shortcuts will not be available.

 

Remove hyperlinks in selection

Module method:

Sub RemoveHyperlinksInSelection

 

       Selection.Hyperlinks.Delete

 

End Sub

Immediate Window method:

Selection.Hyperlinks.Delete

 

Remove hyperlinks in active sheet

Module method:

Sub RemoveHyperlinksInActiveSheet()

 

    ActiveSheet.UsedRange.Hyperlinks.Delete

 

End Sub

Immediate Window method:

ActiveSheet.UsedRange.Hyperlinks.Delete

 

Remove hyperlinks in selection

Module method:

Sub RemoveHyperlinksInActiveWorkbook()

 

    Dim ws As Worksheet

 

    For Each ws In ActiveWorkbook.Worksheets

 

        ws.UsedRange.Hyperlinks.Delete

 

    Next ws

 

End Sub

Immediate Window method:

For Each ws In ActiveWorkbook.Worksheets: ws.UsedRange.Hyperlinks.Delete: Next ws