Macro Mondays - How To Write A Macro To Delete All Hyperlinks In Your Excel Workbook

Hello Excellers, time for some more Excel VBA macro Fun. It is time for another #Excel #macro Monday post in my 2020 series. I want to answer a question from a subscriber. They needed to delete all of their hyperlinks. They had a worksheet with 50+ tabs and hyperlinks on all of them.

The usual method of deleting hyperlinks was not working

select the cells that contain the hyperlinks
Right Click
Delete Hyperlinks

You would expect maybe to be able to simply group all of your worksheets and then repeat the actions above on multiple worksheets, this is not the case, unfortunately. It is a very manual process.

So, there are two options.

You can either repeat the above procedure for all of your worksheet tabs, but if you have over 50 then it would have become a chore,
Write a tiny bit of Excel VBA code, (a Macro) to remove the hyperlinks all at once with the click of a mouse.

This is an Excel VBA Blog. So, of course, we are going to choose Option 2.

What Does The Macro Do?

This macro will delete all hyperlinks in your current workbook.

How Does It Work?

First, open Visual Basic – by hitting ALT +F11 or Developer Tab. You can store this Macro in the current workbook, or if you wanted to reuse the code you can store it in your Personal Macro workbook.

Step 1. We need to declare a variable – ws as Worksheet. This simply creates a memory container for the worksheets.

Step 2. Excel loops through each of the worksheets in the current workbook and deletes any hyperlinks present in the used ranges of the worksheets.

Step 3. Once Excel has looped through all of the worksheets the Macro ends.

Step 4. Test your Macro!

Want To Copy The Code?

[stextbox id=”grey”]

‘macro by howtoexcelatexcel.com

Sub RemoveAllHyperlinks()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
ws.UsedRange.Hyperlinks.Delete
Next ws

End Sub[/stextbox]

If you want to see all of my blog posts in the Macro Mondays Series you can find them all in the link below. Why not bookmark it?, Yes it is updated EVERY Monday.