You must have come across this challenge, when you copy paste some data from websites, from emails or any documents in excel. e.g. if you copy paste the text www.pdexcel.com in excel file, it will be copied as hyperlink, as it is website address. Even if you type email id in excel cell and press enter, the text will turn into hyperlink.
In our example, we are using a sample data to check if our code will remove the hyperlinks.
| First Name | Last Name | email id |
| Little | John | Little.John@gmail.com |
| Big | Tom | Big.Tom@gmail.com |
| Little | Mary | Little.Mary@gmail.com |
| Big | Gary | Big.Gary@gmail.com |
Copy paste the above table in excel file, we can see that email id column has hyperlink, and whenever you will try to select any of the email id column cells, the click will open a new email compose item (if you are using any default connected email application for emails). Below is the screenshot from Microsoft outlook:
To avoid the above action, we will need to remove the hyperlinks and to do it manually, we will need to select all cells, right click each cell and select remove hyperlinks.
In our sample data there are only 4 records and we have structured data. But when we have large data and we need to search and remove hyperlinks, it will become difficult to remove all hyperlinks. Also, it will be a very time consuming task.
To avoid all this, we can just create a small macro, which can be used to removed all hyperlinks within the sheet or entire workbook with just one click of mouse :-).
Check out below vba codes and try them in your excel.
–Code start
Sub removeallhyperlinks()
‘the below code will remove all hyperlinks from the active excel sheet on screen
ActiveSheet.Hyperlinks.Delete
End Sub
–Code end
That’s it, a single line code to remove all hyperlinks in the worksheet :-).
Thank you for reading. Happy Learning !