Clearing Hyperlinks in Excel 2010

  • Article

Office Quick Note banner

Handy Programming Tips for Microsoft Excel 2010: Learn how to clear hyperlinks and hyperlink formatting in a Microsoft Excel 2010 workbook.

Applies to: Excel 2010 | Office 2010 | VBA

In this article
Add the Code to the Visual Basic Editor
Test the Solution
Next Steps

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:

  • Add the Code to the Visual Basic Editor

  • Test the Solution

Add the Code to the Visual Basic Editor

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

  1. Start Excel 2010.

  2. On the Developer tab, click Visual Basic to open the Visual Basic Editor.

    Note

    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.

  3. In the Projects pane, click Sheet1.

  4. 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:="https://www.microsoft.com/", _
        TextToDisplay:="Microsoft"
    Hyperlinks.Add Range("A2"), _
        Address:="https://msdn.microsoft.com", _
        TextToDisplay:="Microsoft Developer Network"
    Hyperlinks.Add Range("A3"), _
        Address:="https://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

Test the Solution

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

  1. Drag the Visual Basic Editor window to the right side of your monitor.

  2. Drag the Excel window to the left side of your monitor and adjust the windows until you can see them both.

  3. While still in the Visual Basic Editor, press F8 to step through the code line-by-line and watch the effects in the worksheet.

Next Steps