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.