Invisible hyperlinks will sometimes cause trouble to you. And in this article, we will use VBA to find invisible hyperlinks in your Excel worksheet.
In this image below, you can see that all the cells have the same format. But there exist some invisible hyperlinks among those cells.
When you need to select a cell, you will certainly use your mouse and click it. But if the cell contains an invisible hyperlink, the computer will immediately launch the browser and open the hyperlink. This is very irritating because you just want to select the cell. Hence, you need to find the invisible hyperlinks to avoid such problem.
Find Invisible Hyperlinks
In order to find those invisible hyperlinks, you can now follow the steps below.
- Press the shortcut keys “Alt +F11” on the key board.
- And then in the Visual Basic editor, click the button “Insert” in the toolbar.
- After that, choose the option “Module” in the sub menu.
- Now input the following codes into the new module:
Sub FindHyperlinks()
Dim h As Range
For Each h In ActiveSheet.UsedRange
If h.Hyperlinks.Count = 1 Then
h.Interior.Color = vbYellow
End If
Next h
End Sub
In these VBA codes, we use the Count method to judge if there exists a hyperlink. If there is a hyperlink, the cell will be filled with yellow color. If you need to find hyperlinks in a specific range, you can also modify the range in the codes.
- Next press the button “F5” on the keyboard. Besides, you can also click the button “Run Sub” in the toolbar in the editor.
And then you can come back to the worksheet. Here all the cells with hyperlinks are filled with a special color. Thus, you can be more careful when you need to select those cells.
In addition, if you need to remove those hyperlinks, you may refer to our previous article 3 Practical Methods to Remove Hyperlinks in Excel Cells. But if you need to directly delete hyperlinks by VBA quickly, you can continue reading the following part.
Remove Invisible Hyperlinks
In this part, you can use VBA codes to remove invisible hyperlinks quickly. But before you do this, you need to make sure that you will not use those hyperlinks.
- Repeat the first 3 steps and insert a new module.
- Now input the following codes into the module:
Sub RemoveHyperlinks()
Dim h As Range
For Each h In ActiveSheet.UsedRange
If h.Hyperlinks.Count = 1 Then
h.Hyperlinks.Delete
End If
Next h
End Sub
- After that, run the sub. Thus, all the hyperlinks will be removed from the worksheet.
You can also come back to the worksheet and adjust the format in the worksheet. Through this process, you can quickly remove all the invisible hyperlinks.
Run a Powerful Repair Tool
When you find your Excel corrupts, you should try to fix Excel immediately. That is because the result of a data corruption can be disastrous. In order to repair xlsx file problem, you can now invest our third party tool. This tool has been used by many Excel file corruption victims. Almost all of them are satisfied with the result. Therefore, do not miss this software.
Author Introduction:
Anna Ma is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including repair doc document damage and outlook repair software products. For more information visit www.datanumen.com