Hi Excellers, time for some more VBA Fun, this time I want to answer a question form a subscriber. 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.
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 be 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 a 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 re use 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 for the worksheets.
Step 2. Excel loops though 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 though all of the worksheets the Macro ends.
Step 4. Test your Macro!
Want To Copy The Code?
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 book mark it?, Yes it is updated EVERY Monday.