[Excel VBA] How to Bulk Delete Hyperlinks (Entire Sheet vs. Specific Ranges)

Introduction

In this article, I will explain how to use VBA to bulk delete hyperlinks in Excel. I will cover two scenarios: deleting links from the entire sheet and deleting links from specific cell ranges.

Common Use Cases:

• You want to remove unnecessary links from data copied from external sources (like the web).
• You want to delete only the link but keep the cell text and formatting.
• You want to remove links from a specific area while leaving other cells untouched.

How to Delete Hyperlinks from the Entire Sheet

The following code deletes all hyperlinks existing on the active sheet at once.

Sub DeleteAllHyperlinksInSheet()

    ActiveSheet.Hyperlinks.Delete

End Sub

Explanation:

Using ActiveSheet.Hyperlinks.Delete removes only the link information while keeping the displayed text as it is. Cell values, background colors, and fonts are preserved.

How to Delete Hyperlinks from a Specific Range

Range 1: Delete links in B2:B4 (Using the Hyperlinks Collection)

Sub DeleteHyperlinksInRange1()

    Dim targetRange As Range
    Set targetRange = ActiveSheet.Range("B2:B4")
    
    targetRange.Hyperlinks.Delete

End Sub

Range 2: Delete links in B5:B6 (Using the ClearHyperlinks Method)

Sub DeleteHyperlinksInRange2()

    Dim clearRange As Range
    Set clearRange = ActiveSheet.Range("B5:B6")
    
    clearRange.ClearHyperlinks

End Sub

Comparison of Methods

Method	Features
.Hyperlinks.Delete	The text in the cell remains. Formatting is also maintained.
.ClearHyperlinks	Cell content or formatting might be cleared depending on the Excel version. Use with caution.

Summary

By using Excel VBA, you can bulk delete hyperlinks for an entire sheet or specific cell ranges. This feature is extremely useful for formatting data with links or removing unnecessary links from copied content.

This method is effective when:

• Data copied from the web contains a large number of links.
• You want to remove only specific links within a file.
• You need to remove links from work reports or deliverables.

Choose between .Delete and .ClearHyperlinks according to your specific needs to achieve efficient data formatting.