Remove Hyperlinks with Excel VBA

Objective:

We will write an Excel VBA code to remove all hyperlinks from a worksheet (or a specific range) in Excel. Hyperlinks are often used in Excel cells, but there might be cases where you want to remove them without affecting the cell’s content.

Concepts Involved:

1. Hyperlinks in Excel:
   • A hyperlink is a reference to a web page or an address in Excel. Hyperlinks in Excel can be inserted via the ribbon or VBA, and they allow users to click on the cell to navigate to a specific URL or another sheet.
   • When you remove a hyperlink, Excel will remove the link, but leave the cell’s contents (text, number, etc.) intact.
2. VBA Code Structure:
   • ActiveSheet: Refers to the sheet that is currently selected in the Excel workbook.
   • Hyperlinks Collection: Each worksheet in Excel has a collection of hyperlinks. The Hyperlinks property allows access to this collection.
   • Remove Method: This is used to remove the hyperlink from the cell.

The Code:

Here is the VBA code to remove all hyperlinks from the active worksheet:

Sub RemoveAllHyperlinks()
    ' Declare a variable to reference the worksheet
    Dim ws As Worksheet   
    ' Set the current worksheet as the target
    Set ws = ActiveSheet   
    ' Check if there are any hyperlinks on the sheet
    If ws.Hyperlinks.Count > 0 Then   
        ' Remove all hyperlinks on the worksheet
        ws.Hyperlinks.Delete       
        ' Provide feedback to the user
        MsgBox "All hyperlinks have been removed.", vbInformation       
    Else
        ' In case no hyperlinks were found
        MsgBox "No hyperlinks found on this worksheet.", vbExclamation
    End If  
End Sub

Explanation of the Code:

1. Sub RemoveAllHyperlinks():
   This is the starting point of the macro. It is the name of the subroutine that will execute the process of removing hyperlinks.
2. Dim ws As Worksheet:
   This declares a variable ws that will hold a reference to the worksheet from which we will remove hyperlinks. This makes the code more flexible if you want to change which worksheet to act on.
3. Set ws = ActiveSheet:
   This assigns the currently active sheet (the sheet that is selected when you run the macro) to the ws variable. You can change ActiveSheet to a specific sheet (like Sheets(« Sheet1 »)) if you need to target a particular sheet.
4. If ws.Hyperlinks.Count > 0 Then:
   This checks whether there are any hyperlinks on the active worksheet. The Hyperlinks.Count property returns the number of hyperlinks in the worksheet. If there is at least one hyperlink, it proceeds to the next step to remove them.
5. ws.Hyperlinks.Delete:
   This line removes all hyperlinks on the worksheet. The Delete method of the Hyperlinks collection removes each hyperlink in the collection.
6. MsgBox « All hyperlinks have been removed. », vbInformation:
   A message box is displayed after the hyperlinks are removed. This provides feedback to the user, confirming that the task was completed successfully. The vbInformation constant shows an informational icon.
7. Else Block:
   If there are no hyperlinks to remove (ws.Hyperlinks.Count is 0), the code enters the Else block and displays a message box informing the user that no hyperlinks were found.
8. MsgBox « No hyperlinks found on this worksheet. », vbExclamation:
   If no hyperlinks are found, this message box is shown with a warning icon (vbExclamation), telling the user that no hyperlinks were present on the worksheet.
9. End Sub:
   Marks the end of the subroutine.

How to Use This Code:

1. Open Excel:
   Open your Excel workbook.
2. Access the VBA Editor:
   • Press Alt + F11 to open the Visual Basic for Applications (VBA) editor.
3. Insert a Module:
   • In the VBA editor, click Insert in the top menu, then choose Module.
   • This will insert a blank module where you can paste the code.
4. Paste the Code:
   • Copy the VBA code provided above and paste it into the blank module.
5. Run the Code:
   • Press F5 (or choose Run from the toolbar) to execute the macro.
   • The code will remove all hyperlinks from the active sheet and display a message confirming the action.

Removing Hyperlinks from a Specific Range:

If you only want to remove hyperlinks from a specific range, you can modify the code to target that range. For example:

Sub RemoveHyperlinksInRange()
    ' Declare variables for the worksheet and the range
    Dim ws As Worksheet
    Dim rng As Range   
    ' Set the worksheet to the active sheet
    Set ws = ActiveSheet   
    ' Set the range (for example, A1 to D10)
    Set rng = ws.Range("A1:D10")   
    ' Remove hyperlinks in the specified range
    rng.Hyperlinks.Delete  
    ' Provide feedback
    MsgBox "Hyperlinks in the specified range have been removed.", vbInformation   
End Sub

Conclusion:

This macro effectively removes all hyperlinks from a worksheet (or a specific range) without modifying any other content in the cells. It is a simple and efficient way to clean up your Excel sheets by removing unwanted hyperlinks.