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.
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
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
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.