Supposing you have multiple URLs in your worksheet, but they are not linked, and now you need to convert all the unlinked urls to clickable hyperlinks. Of course, you can double click them one by one to make them clickable, but this will be time consuming if there are lots of URLs. How could you convert multiple un-linked URLs to clickable hyperlinks automatically in Excel, using VBA?
To solve this problem you can use VBA code.
Sub ConvertToHyperlinks() 'Updateby20140318 Dim Rng As Range Dim WorkRng As Range On Error Resume Next xTitleId = "KutoolsforExcel" Set WorkRng = Application.Selection Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8) For Each Rng In WorkRng Application.ActiveSheet.Hyperlinks.Add Rng, Rng.Value Next End Sub
Create the Macro
Here are the step required to create the macro.
- Select the range of url text that you want to convert to clickable hyperlinks.
- Hold down the ALT + F11 keys to open the Microsoft Visual Basic for Applications window.
- Click Insert > Module, and paste the above code in the Module Window.
- Press the F5 key to run the code, a pop up dialog for you to select a range, then click OK, and the selected un-linked URLs have been converted to the clickable hyperlinks.
The above code is from the website ExtendOffice. I have tested this macro and it indeed does work.