Hello, Excellers. Welcome back t another #MacroMonday Excel blog post in my 2020 macro Series. I recently had this question submitted via email from a subscriber. How to remove all hyperlinks. They had a LOT of hyperlinks in an Excel workbook (with 50+ sheets). They needed to quickly delete the hyperlinks as they were no longer relevant. It is easy to remove multiple hyperlinks from a single worksheet, but removing hyperlinks from multiple worksheet tabs requires a little more work. Let’s work through them both.
Remove Hyperlinks From A Single Worksheet.
- Select your range of cells that contain the hyperlinks
- Right Click | Remove Hyperlinks.
- All selected hyperlinks are deleted.
Ok, so that is straightforward enough right?.
Remove Hyperlinks From Multiple Worksheets.
So, based on the above way of working maybe you would expect maybe to be able to simply group all of your worksheets then delete the hyperlinks?. Unfortunately, this is not the case.
You will need to repeat the above procedure for all of your worksheet tabs. But, if you have over 50 worksheets for example then it would become a chore So, instead we can write a tiny bit of Excel VBA code, (a Macro) to remove the hyperlinks all at once with the click of a mouse. Sound good?. Good!. Here is how simple it is.
Starting The VBA Macro.
First, you will need to open the Visual Basic Editor. There are two ways to do this.
- Either by hitting ALT +F11 or
- Selecting the Developer Tab | Code Group | Visual Basic. Both methods have the same result.
If you do not see the Developer Tab you may need to enable it. Please watch my YouTube video on how to do this below.
Before you begin to write any code you need to decide where to store the code. You have a choice.
- To store your code either in your Personal Macro Workbook or
- Store it in your current workbook
What Is The Difference In The Locations?.
So, there is a difference. If you save the code in your Personal Macro workbook it will be available in any Excel workbooks. If you store it in the current workbook then use is restricted to that workbook. In this instance, I may want to reuse the code so I will store it in my Personal Macro Workbook.
When you select this option then Excel creates (if it is not already created) this workbook and saves the macro in that location. By default, this macro workbook is named Personal.xlsb. This is a really useful macro that I want to reuse over and over again. So, I will make sure to save this in my Personal Macro Workbook.
If you want more details on creating and updating your personal macro workbook then I recommend my blog posts below.
Creating and Updating Your Personal Macro Workbook
Why Is My Personal Macro Workbook Not Loading Automatically?
Create A Shortcut To Your Personal Excel Macro Workbook
Write The Code To Remove Hyperlinks.
So, I have inserted a New Module into my Personal Macro Workbook. Type Sub then the name of the Excel macro. In this example, I have named my Macro RemoveAllHyperlinks.
As you type the name of the Macro and hit return, Excel will automatically insert End Sub. Now all that is needed is the rest of the code in between these two lines of code.
[stextbox id=’info’]
Sub RemoveAllHyperLinks
End Sub
[/stextbox]
Declaring Variables.
First, I need to declare a variable in this macro. This simply creates a memory container for the values. I have created the variable as below to store the worksheet names.
[stextbox id=’info’]
Dim ws as As Worksheet
[/stextbox]
Use The For Each Loop.
The line of code will loop through each of the worksheets in the current workbook. Looping is usually used when we need to perform the same activity over and over again in the workbook.
[stextbox id=’info’]
For Each ws In ThisWorkbook.Worksheets
[/stextbox]
Remove All Hyperlinks.
The next link of code looks at the used range in each of the worksheets. If the used range in each of the worksheets contains hyperlinks they are deleted.
[stextbox id=’info’]
ws.UsedRange.Hyperlinks.Delete
Next ws
[/stextbox]
Ending The Macro.
Once of all of the looping of worksheets has been completed then the code finally ends. This instruction was already entered into the module for us when started the type the name of the Macro.
[stextbox id=’info’]
End Sub
[/stextbox]
Copy The Full Excel Macro.
Here is the complete Macro code. You have been able to remove all hyperlinks with a small bit of Excel VBA code.
[stextbox id=’info’]
Sub RemoveAllHyperlinks()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
ws.UsedRange.Hyperlinks.Delete
Next ws
End Sub
[/stextbox]
Macro Monday Blog Posts.
