Microsoft Office

So here is the scenario, you have an Excel doc full of plain text links and you need to convert them to clickable links. Normally I would have to click on each cell and hit Enter, this can be very time consuming. Using a small macro you can automate this task. For any of you that have not used Macros before they can take many forms, but in this case it is a few lines of code you can trigger on a selected set of cells.

The Macro Code – Convert_To_Hyperlinks()


Public Sub Convert_To_Hyperlinks()
Dim Cell As Range
For Each Cell In Intersect(Selection, ActiveSheet.UsedRange)
If Cell <> "" Then
ActiveSheet.Hyperlinks.Add Cell, Cell.Value
End If
Next
End Sub

Creating The Macro

  1. Open your Excel doc
  2. Open the macro editor by pressing ALT+F11.
  3. In the Tools Menu, left-click View and select Project Explorer.
    View -> Project Explorer (CTRL+R)
  4. Right-click the Project you are working on.
  5. Right-click ‘Modules’, select ‘Insert’, then select ‘Module’.
    Modules -> Insert -> Module
  6. Paste the code into the project module you have selected.
  7. Press ALT+F11 to return to your Excel workbook (or click on its icon in the Windows taskbar).

Run The Macro

  1. To execute the macro, select the unclickable text links you want to convert to clickable hyperlinks.
  2. Press ALT+F8 to open the Macro selector window and click on the macro you just created.
  3. Your Links are now all Clickable! Saving you time and data entry fatigue 🙂

Useful Excel Links

Leave a comment