How to remove hyperlinks in excel sheet using VBA code ?

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:

new email

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.

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 !

Please leave a reply if you like the article or if you need any help on excel

%d bloggers like this: