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;
- 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;
- 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).
- 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 NowNext up:
- Your essential guide to performance feedback
- Data Recovery in Windows Server 2016
- Interviewing and avoiding the artful dodger!
- Life has many, many stations. Having trouble getting to your next one?
- Updates to Angular 2 that makes coding easier – Custom Pipes
- Do a quick forecast in Excel 2016
- Outlook on the Web in Exchange Server 2016
- Does good design sell itself?
- I'll have courtesy with my gelato, thanks.
- 6 conversations a new manager should have
Previously
- Your essential guide to performance feedback
- PRINCE2® Agile – Enhancing your project delivery
- Working with Styles – Part 5
- Microsoft Project 2013 & 2016: Levelling Gantt
- Make multiple copies of a Microsoft Excel spreadsheet in seconds
- What Sort of Leader are You?
- Beguile for a while with a smile
- Exchange Server 2016 features and updates – Part 2
- “How was the training?”…“Yeah good thanks, now what’s for lunch?”
- Exchange Server 2016 features and updates – Part 1