I recently had this question submitted via email from a subscriber, they had a LOT of hyperlinks in an Excel workbook (with 50+ sheets), and they wanted to quickly remove all of 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.
1. Remove Hyperlinks From A Worksheet.
- Select your range of cells that contain the hyperlinks
- Right Click | Remove Hyperlinks
2. Remove Hyperlinks From Multiple Worksheets.
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. You can either repeat the above procedure for all of your worksheet tabs, but if you have over 50 then it would be become a chore, 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?. Then here how simple it is.
- Hold down the ALT + F11 keys to open the Microsoft Visual Basic for Applications window
- Click Insert > Module, and paste the following code in the Module Window
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
- Then press F5 key to run this code, and all the hyperlinks are removed from the active worksheet immediately.
Let’s talk through the code.
1. First we need to declare a variable ws as worksheet. This simply creates a memory container for the worksheets.
2. Excel then will loop through each of the worksheets in the current workbook and delete any hyperlinks that are present in the used ranges of the worksheets.
3. Once all of the worksheets have been looped through the macro ends
If you want more Excel and VBA tips then sign up to my monthly Newsletter where I share 3 Excel Tips on the first Wednesday of the month and receive my free Ebook, 30 Excel Tips.
If you want to see all of the blog posts in the Macro Mondays Series or the example worksheets you can do so by clicking on the links below.
Don’t forget to SUBSCRIBE to the How To Excel At Excel Newsletter for more tips the first Wednesday of the month.