Find and Remove Hyperlinks in Excel using VBA

Why would need to remove these links from the worksheet, depends on your requirement. It is sometimes a difficult and time taking process to find hyperlinks, which are scattered all over the worksheet. Therefore, you need a program that would quickly find the unwanted hyperlinks and remove it with press of a button.

Note: You can use the Find & Replace option under Home tab in your Excel file. However, it requires lot of inputs and option selections.

Using a VBA program, I can remove all hyperlinks from a sheet, or I can find specific (unwanted) links and quickly remove it. Yes, in case of specific hyperlinks, the program has to loop through the rows of particular (or all) columns, find the links and remove it.

I’ll show you three different ways to get rid of hyperlinks on your worksheet.

Remove All Hyperlinks in a Sheet

In my example, I’ll add an ActiveX Button control. The click event will perform the find and remove operation.

There are two ways you can remove all hyperlinks in a particular sheet. It would not check for any condition. Just remove all of them.

Option Explicit

Private Sub CommandButton1_Click()

    ActiveSheet.Hyperlinks.Delete
    
End Sub

If the hyperlinks are in Sheet1, write the above code in Sheet1 object in your VBA project. You can double click the button to open the VBA project. The default event of a button is Click(). Write the code inside the click event.

Here, I am using the ActiveSheet object to delete all the hyperlinks. This is the first method.

In the second method, I’ll use the Cells() method of the Range class.

Private Sub CommandButton1_Click()

     Cells.Hyperlinks.Delete
    
End Sub

This will do what the previous method did. Delete all hyperlinks in sheet1. The Cells() method takes two parameters. However, I have not passed any, and therefore, it would delete all the links.

Similar example: How to easily Find and Highlight Duplicate values in Excel using VBA

Remove Hyperlink in a Particular Cell of a Sheet

Let’s now take this to the next level and make it more dynamic and useful. I wish to remove (or delete) a hyperlink in a particular Cell. To do this, I can use the Cells() method again. This time I’ll pass parameters to the method.

Private Sub CommandButton1_Click()

    Cells(6, 1).Hyperlinks.Delete
    
End Sub

The parameters are RowIndex and ColumnIndex. The first is row 6 and second is column 1 (that is A). Any hyperlink in the 6th row will be removed.

Remember: It just removes the hyperlink and the text will remain as it is.

Remove Specific Hyperlinks in a Range using Cells() Method

Now we know that the above explained Cells() method takes parameters. Therefore, we can pass parameters dynamically. You can look through the entire sheet (or a range) to find unwanted hyperlinks and remove them all.

I am using the InputBox() method to prompt the user to enter the name of the link, then search the value in a particular range and remove the links.

Option Explicit

Private Sub CommandButton1_Click()
On Error GoTo ErrHandler
    
    Dim sSearch As String
    sSearch = InputBox("Enter a text", "Hyperlink")   ' ASK FOR A VALUE (LINK).
    
    If Trim(sSearch) <> "" Then
        Dim myDataRng As Range
        Dim cell As Range
         
         THE RANGE WHERE IT WILL LOOK THE LINKS.
        Set myDataRng = Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row)
        
        ' LOOP THROUGH EACH ROW.
        For Each cell In myDataRng
            If Trim(cell(myDataRng.Row, 1).Text) = "www." & sSearch & ".com" Then
                Cells(cell.Row, 1).Hyperlinks.Delete        ' Delete the link.
            End If
        Next cell
         
        Set myDataRng = Nothing
    End If
ErrHandler:
    '
End Sub

When you click the button, it will pop open an InputBox and you need to enter a value in it. For example, if I wish to find “example.com”, I will simply enter the value “example”. You do not have to enter .com or www. in the input box. You can further modify the code according to your requirement.

Well, that’s it. You now have a code that will quickly and efficiently find and remove unwanted hyperlinks from your worksheet. You do not have to break your head anymore to find the links one by one to get rid of the links.

← PreviousNext →