The macro in this blog post lists all hyperlinks in a worksheet. It also lists all words beginning with "http" or "www". It grabs the hyperlink even if you have a hyperlink function in a cell.

Example, this sheet has two regular hyperlinks (B2:B3) and one hyperlink function (B5) and two web addresses (B7).

list all hyperlinks in a sheet1

This is what the macro returns, the worksheet name in column A. Cell address in column B and the hyperlink in column C.

list all hyperlinks in a sheet2

VBA Macro

Sub ListHyperlinks()
Set Asheet = ActiveSheet
Set Nsheet = Sheets.Add
Nsheet.Range("A1:C1") = Array("Worksheet", "Address", "Hyperlink")
Nsheet.Range("A1:C1").Font.Bold = True
i = 0
For Each cell In Asheet.UsedRange
    On Error Resume Next
    lnk = cell.Hyperlinks(1).SubAddress
    If Err = 0 Then
        Nsheet.Range("A2").Offset(i, 0) = Asheet.Name
        Nsheet.Range("B2").Offset(i, 0) = cell.Address
        Nsheet.Range("C2").Offset(i, 0) = cell.Hyperlinks(1).Address
        i = i + 1
    Else
        If Left(cell.Formula, 11) = "=HYPERLINK(" Then
            strArray = Split(cell.Formula, Chr(34))
            Nsheet.Range("A2").Offset(i, 0) = Asheet.Name
            Nsheet.Range("B2").Offset(i, 0) = cell.Address
            Nsheet.Range("C2").Offset(i, 0) = strArray(1)
            i = i + 1
        Else
            strArray = Split(cell)
            For Each vl In strArray
                If Left(vl, 4) = "http" Or Left(vl, 3) = "www" Then
                    Nsheet.Range("A2").Offset(i, 0) = Asheet.Name
                    Nsheet.Range("B2").Offset(i, 0) = cell.Address
                    Nsheet.Range("C2").Offset(i, 0) = vl
                    i = i + 1
                End If
            Next vl
        End If
    End If
    On Error GoTo 0
Next cell
Nsheet.Columns("A:C").AutoFit
End Sub

Where to put the code?

  1. Press Alt +F11
  2. Insert a module
  3. Paste vba code to code window
  4. Exit VB Editor Alt + Q

How to start macro?

  1. Press Alt+F8
  2. Click "ListHyperlinks"
  3. Click "Run" button

What happens when I run the macro?

  1. New sheet is inserted
  2. Sheet is populated with hyperlink data

How do I find all hyperlinks in a workbook?

This macro allows you to find links in all worksheets in active workbook.

Sub ListHyperlinksInWB()
Set Nsheet = Sheets.Add
Nsheet.Range("A1:C1") = Array("Worksheet", "Address", "Hyperlink")
Nsheet.Range("A1:C1").Font.Bold = True
i = 0
For Each sh In ActiveWorkbook.Worksheets
    If sh.Name <> Nsheet.Name Then
        For Each cell In sh.UsedRange
            On Error Resume Next
            lnk = cell.Hyperlinks(1).SubAddress
            If Err = 0 Then
                Nsheet.Range("A2").Offset(i, 0) = sh.Name
                Nsheet.Range("B2").Offset(i, 0) = cell.Address
                Nsheet.Range("C2").Offset(i, 0) = cell.Hyperlinks(1).Address
                i = i + 1
            Else
                If Left(cell.Formula, 11) = "=HYPERLINK(" Then
                    strArray = Split(cell.Formula, Chr(34))
                    Nsheet.Range("A2").Offset(i, 0) = sh.Name
                    Nsheet.Range("B2").Offset(i, 0) = cell.Address
                    Nsheet.Range("C2").Offset(i, 0) = strArray(1)
                    i = i + 1
                Else
                    strArray = Split(cell)
                    For Each vl In strArray
                        If Left(vl, 4) = "http" Or Left(vl, 3) = "www" Then
                            Nsheet.Range("A2").Offset(i, 0) = sh.Name
                            Nsheet.Range("B2").Offset(i, 0) = cell.Address
                            Nsheet.Range("C2").Offset(i, 0) = vl
                            i = i + 1
                        End If
                    Next vl
                End If
            End If
            On Error GoTo 0
        Next cell
    End If
Next sh
Nsheet.Columns("A:C").AutoFit
End Sub

Download excel *.xlsm file

List hyperlinks.xlsm

Tip! If you want to stop automatic hyperlinking (excel 2010) follow these steps

  1. Click "File" on the ribbon
  2. Click "Options"
  3. Click "Proofing"
  4. Click "AutoCorrect Options..."
  5. Go to "Autoformat as you type" on the menu
  6. Disable "Internet and network paths with hyperlinks"

Stop automatic hyperlinking