Remove all hyperlinks from an Excel sheet using VBA

 Oct 13, 2016

You can remove all hyperlinks in an Excel sheet by writing only one statement in VBA and that’s:

ActiveSheet.Hyperlinks.Delete

Of course if you were to remove hyperlinks from a range you would replace the ActiveSheet with the specified range, like:

Range(“G6:K12”).Hyperlinks.Delete

Or

Selection.Hyperlinks.Delete

unfortunately, this process will remove all the formatting from the cells where hyperlinks have been removed. One way to prevent this from happening is to handle the removal of hyperlinks, cell by cell, preserving the format of the individual cell before the removal, and then pasting it back after the removal.

So, here we are going to develop such a process in VBA.

The process creates a temporary sheet, loops through all hyperlink cells, in each iteration it copies the cell to the temporary sheet, removes its hyperlink, and then reapplies the format back from the copy. After the loop completes, it removes the temporary sheet.

Here’s the complete code;

The comments make the code self-explanatory, however 3 points are worth mentioning;


  1. Every time a sheet is added, it becomes the ActiveSheet and so we lose our current ActiveSheet. To avoid this we need to preserve the name of the current ActiveSheet, make the new sheet and then activate the previous sheet using its name. This is done in the first few lines;
  2. To add the sheet at the end, i.e. after the last sheet, you add it using the “After” parameter shown on line 7 above. If there are 5 sheets then the last sheet is Sheets(5) because Sheets.Count is 5, therefore the last sheet is always Sheets(Sheets.Count).
  3. Within the last few lines I have used Application.DisplayAlerts = False to disable the confirmation prompt that appears every time you delete a sheet. I delete the sheet and then will enable it.

For more informaiton, take a look at New Horizons' Excel Training courses.



How do your Excel skills stack up?   

Test Now  

How do your Excel skills stack up?   

Test Now  

About the Author:

Cyrus Mohseni  

Having worked within the education and training industry for over 15 years as well as the IT industry for 10 years, Cyrus brings to New Horizons a wealth of experience and skill. Throughout his career he has been involved in the development and facilitation of numerous training programs aimed at providing individuals with the skills they require to achieve formal qualification status. Cyrus is a uniquely capable trainer who possesses the ability to connect with students at all levels of skill and experience.

Read full bio
top
Back to top