In Excel, I pasted in a bunch of URLs, but they aren't live links -- how do I bulk convert them to live links?
In Excel, I pasted in a bunch of URL's into a column.
However, they aren't live links after pasting.
How do I bulk convert them all to live links swiftly?
This should do the trick.
Sub Test()
Dim rngRange As Range
For Each rngRange In Selection
If Trim(rngRange.Value) <> "" Then
rngRange.Hyperlinks.Add _
Anchor:=Cells(rngRange.Row, rngRange.Column), _
Address:=rngRange.Text, _
ScreenTip:=rngRange.Text, _
TextToDisplay:=rngRange.Text
End If
Next rngRange
End Sub
If you have to do it a lot, I'd hook it up to a keyboard shortcut.
To make this happen in Excel 2010 (compliments of @Excellll)
- Go to "View" and then click the "Macros" button.
- This will open a dialog box asking for the name of a new macro. Enter "Test" and click "Create".
- This will open the VBA editor. Copy the code above into this new window.
- Now go to your spreadsheet and select the range of cells with the hyperlinks you want to fix.
- Go back to the VBA editor. Press F5. This will execute the code.