Clearing Hyperlinks in Excel 2010
Handy Programming Tips for Microsoft Excel 2010: Learn how to clear hyperlinks and hyperlink formatting in a Microsoft Excel 2010 workbook.
Last modified: May 05, 2011
Applies to: Excel 2010 | Office 2010 | VBA
Published: May 2011
Provided by: Frank Rice, Microsoft Corporation
Hyperlinks are a useful and convenient way to move between objects. Hyperlinks are colored and underlined text or graphics that you click to go to a file, a specific location in a file, a webpage on the Internet, or a webpage on an intranet. In this topic, you work with the Range.ClearHyperlinks and Range.ClearFormats methods to clear hyperlinks and hyperlink formatting. To complete this task, you must do the following:
In this task, you add programming code that adds hyperlinks to the worksheet, clears the hyperlinks (but leaves the text and formatting intact), and then clears the formatting.
To add code to the Visual Basic Editor
Start Excel 2010.
On the Developer tab, click Visual Basic to open the Visual Basic Editor.
If you do not see the Developer tab in Excel 2010, click the File tab, and then click Options. In the categories pane, click Custom Ribbon, select Developer, and then click OK.
In the Projects pane, click Sheet1.
Paste or type the following Microsoft Visual Basic for Applications (VBA) code into the module window.
Sub TestClearHyperlinks() ' Add a few hyperlinks, and then clear the hyperlinks in a particular range. Hyperlinks.Add Range("A1"), _ Address:="http://www.microsoft.com/", _ TextToDisplay:="Microsoft" Hyperlinks.Add Range("A2"), _ Address:="http://msdn.microsoft.com", _ TextToDisplay:="Microsoft Developer Network" Hyperlinks.Add Range("A3"), _ Address:="http://msdn.microsoft.com/office", _ TextToDisplay:="Office Developer" Dim rng As Range Set rng = Range("A:A") Columns("A").AutoFit ' Work with a subset of the range of links ' you created: Set rng = Range("A1:A2") ' Note that this command clears the hyperlinks, but ' leaves the cells formatted as if they were hyperlinks. rng.ClearHyperlinks ' In a real application, you might want to also clear the formatting ' for cells that had been hyperlinks that are now cleared: ' This code is optional, of course: rng.ClearFormats End Sub
In this task, you step through the VBA code that adds three hyperlinks to the worksheet, clears the hyperlinks, and then clears the hyperlink formatting.
To run the code
Drag the Visual Basic Editor window to the right side of your monitor.
Drag the Excel window to the left side of your monitor and adjust the windows until you can see them both.
While still in the Visual Basic Editor, press F8 to step through the code line-by-line and watch the effects in the worksheet.