List all hyperlinks in worksheet programmatically
Author: Oscar Cronquist Article last updated on February 13, 2023
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).
This is what the macro returns, the worksheet name in column A. Cell address in column B and the hyperlink in column C.
What's on this webpage
1. VBA Macro- list all hyperlinks in the worksheet
'Name macro
Sub ListHyperlinks()
'The SET statement allows you to save an object reference to a variable
'Save activesheet to object Asheet
Set Asheet = ActiveSheet
'Insert a new worksheet and save to object Nsheet
Set Nsheet = Sheets.Add
'Save values Worksheet, Address, and Hyperlink to cell range A1:C1
Nsheet.Range("A1:C1") = Array("Worksheet", "Address", "Hyperlink")
'Change to bold font
Nsheet.Range("A1:C1").Font.Bold = True
'Save 0 (zero) to variable i
i = 0
'Iterate through each cell in UsedRange
'UsedRange returns a Range object that represents the used range on the specified worksheet.
For Each cell In Asheet.UsedRange
'Enable error handling
On Error Resume Next
'Check if cell has a hyperlink and save the hyperlink to variable lnk
lnk = cell.Hyperlinks(1).SubAddress
'Check if no error has occurred
If Err = 0 Then
'Save worksheet name to column A based on variable i
Nsheet.Range("A2").Offset(i, 0) = Asheet.Name
'Save cell address to column B based on variable i
Nsheet.Range("B2").Offset(i, 0) = cell.Address
'Save hyperlink address to column C based on variable i
Nsheet.Range("C2").Offset(i, 0) = cell.Hyperlinks(1).Address
'Add 1 to variable i and save result to variable i
i = i + 1
'Go here if an error has occurred
Else
'Check if formula begins with "=HYPERLINK("
If Left(cell.Formula, 11) = "=HYPERLINK(" Then
'Split formula and save to variable strArray
strArray = Split(cell.Formula, Chr(34))
'Save worksheet name to column A based on variable i
Nsheet.Range("A2").Offset(i, 0) = Asheet.Name
'Save cell address to column B based on variable i
Nsheet.Range("B2").Offset(i, 0) = cell.Address
'Save hyperlink address to column C based on variable i
Nsheet.Range("C2").Offset(i, 0) = strArray(1)
'Add 1 to variable i and save result to variable i
i = i + 1
'Go here if formulas does not begin with "=HYPERLINK("
Else
'Split cell into an array based on default delimiting value (space character) and save to array variable strArray
strArray = Split(cell)
'Iterate through each value in array strArray
For Each vl In strArray
'Check if array value is equal to http or www
If Left(vl, 4) = "http" Or Left(vl, 3) = "www" Then
'Save worksheet name to column A based on variable i
Nsheet.Range("A2").Offset(i, 0) = Asheet.Name
'Save cell address to column B based on variable i
Nsheet.Range("B2").Offset(i, 0) = cell.Address
'Save hyperlink address to column C based on variable i
Nsheet.Range("C2").Offset(i, 0) = vl
'Add 1 to variable i and save result to variable i
i = i + 1
End If
Next vl
End If
End If
'Disable error handling
On Error GoTo 0
Next cell
'Change column widths for A:C
Nsheet.Columns("A:C").AutoFit
End Sub
2. Where to put the code?
- Press Alt +F11 to open the Visual Basic Editor (VBE).
- Press with left mouse button on "Insert" on the top menu.
- Press with mouse on "Module" to insert a module.
- Paste VBA code to code window.
- Exit VB Editor Alt + Q.
Save workbook with file extension *.xlsm (macro-enabled workbook).
3. How to start macro?
- Press Alt+F8, a dialog box appears.
- Press with left mouse button on "ListHyperlinks" to select the macro name.
- Press with left mouse button on "Run" button to run the selected macro.
4. What happens when I run the macro?
- A new worksheet is inserted.
- The worksheet is populated with hyperlink data.
- Macro ends.
5. How do I find all hyperlinks in a workbook?
This macro allows you to find links in all worksheets in the 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
6. How to disable automatic hyperlinking in Excel?
Tip! If you want to stop automatic hyperlinking (Excel 2010) follow these steps:
- Press with left mouse button on "File" on the ribbon
- Press with left mouse button on "Options"
- Press with left mouse button on "Proofing"
- Press with left mouse button on "AutoCorrect Options..."
- Go to "Autoformat as you type" on the menu
- Disable "Internet and network paths with hyperlinks"
Hyperlinks category
Create links to all sheets in a workbook programmatically
The macro demonstrated above creates hyperlinks to all worksheets in the current worksheet. You will then be able to quickly […]
The macro demonstrated above creates hyperlinks to all worksheets in the current worksheet. You will then be able to quickly […]
Use hyperlinks in a pivot table
Sean asks: Basically, when I do a refresh of the data in the "pivotdata" worksheet, I need it to recognise […]
Sean asks: Basically, when I do a refresh of the data in the "pivotdata" worksheet, I need it to recognise […]
Locate lookup values in an Excel table [HYPERLINK]
Today I'll show you a formula that returns a hyperlink pointing to a location based on a lookup value. When […]
Today I'll show you a formula that returns a hyperlink pointing to a location based on a lookup value. When […]
Worksheet category
Hide specific worksheets programmatically
This article demonstrates techniques to hide and unhide worksheets programmatically. The image above shows the Excel window and the worksheet […]
This article demonstrates techniques to hide and unhide worksheets programmatically. The image above shows the Excel window and the worksheet […]
Excel categories
8 Responses to “List all hyperlinks in worksheet programmatically”
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
I have not had the occasion to work with Hyperlinks in the past, so I cannot be 100% sure this works perfectly, but in my tests, your code and my code seem to return identical results every time, so I have a lot of confidence in my macro. My code has less active lines of code than yours and I believe it will execute slightly faster as it does not look at every cell in the UsedRange, rather, it first looks directly at hyperlinks only, and then it looks at all formulas on the sheet testing them to see if they start with "=HYPERLINK(" or not. Also, my code works slightly different than yours, it is sort of a combination of your two macros. To use it, you first select the sheets you want to process, so if you want a single sheet, select it... if you want only a few sheets, Control+ press with left mouse button on the tabs for the sheets you want... and if you want all the sheets, select the first sheet and then Shift+press with left mouse button on the last sheet. After you have selected the sheets you want to process, then run this macro.
Sub ListHyperlinksToo() Dim N As Long, LastRow As Long, StartCells As Range, Cell As Range Dim NewSheet As Worksheet, WS As Worksheet, HypLnk As Hyperlink, SS As Object On Error GoTo NoHyperlinks Set SS = ActiveWindow.SelectedSheets Sheets(1).Select Set NewSheet = Sheets.Add Set StartCells = NewSheet.Range("A1:C1") StartCells = Array("Worksheet", "Address", "Hyperlink") StartCells.Font.Bold = True For Each WS In SS For Each HypLnk In WS.Hyperlinks N = N + 1 StartCells.Offset(N) = Array(WS.Name, HypLnk.Range.Address, HypLnk.Address) Next N = N + 1 For Each Cell In WS.Cells.SpecialCells(xlFormulas) If Left(Cell.Formula, 11) = "=HYPERLINK(" Then StartCells.Offset(N) = Array(WS.Name, Cell.Address, Split(Cell.Formula, """")(1)) N = N + 1 End If Next Next LastRow = Cells(Rows.Count, "A").End(xlUp).Row StartCells.EntireColumn.Sort Range("A2:A" & LastRow), xlAscending, Range("B2:B" & LastRow), , xlAscending, Header:=xlYes NoHyperlinks: End SubSorry, the above code does not correctly handle interim selected sheets that contain no hyperlinks (it ends the macro too soon). This revised macro does work correctly for those situations, so use it instead of what I posted earlier...
Sub ListHyperlinksToo() Dim N As Long, LastRow As Long, StartCells As Range, Cell As Range Dim NewSheet As Worksheet, WS As Worksheet, HypLnk As Hyperlink, SS As Object Set SS = ActiveWindow.SelectedSheets Sheets(1).Select Set NewSheet = Sheets.Add Set StartCells = NewSheet.Range("A1:C1") StartCells = Array("Worksheet", "Address", "Hyperlink") StartCells.Font.Bold = True On Error GoTo NoHyperlinks For Each WS In SS For Each HypLnk In WS.Hyperlinks N = N + 1 StartCells.Offset(N) = Array(WS.Name, HypLnk.Range.Address, HypLnk.Address) Next N = N + 1 For Each Cell In WS.Cells.SpecialCells(xlFormulas) If Left(Cell.Formula, 11) = "=HYPERLINK(" Then StartCells.Offset(N) = Array(WS.Name, Cell.Address, Split(Cell.Formula, """")(1)) N = N + 1 End If Next Continue: Next LastRow = Cells(Rows.Count, "A").End(xlUp).Row StartCells.EntireColumn.Sort Range("A2:A" & LastRow), xlAscending, Range("B2:B" & LastRow), , xlAscending, Header:=xlYes Exit Sub NoHyperlinks: Resume Continue End SubRick Rothstein (MVP - Excel)
Thank you for your contribution. Always interesting to read your comments.
I have a simple question:
Why post code that has undeclared variables? Most of the time I can figure out what they should be declared as, but in some cases I don't even know that a certain type of variable exists. For example, your variable "lnk" should be declared as...?
Again, my main point is, as a programmer who writes vba code to publish, why publish code that does not work simple because the variables are not declared and not everyone is going to know what to declare them as??
I'd say 'Thanks for the code', but I don't believe that no respectable programmer would make the mistake of coding without variable declarations.
Yoma Ma,
Undeclared variables are automatically variants, I can declare them for you but it won't make any difference except that your macro grows a line or more.
If you think the variables will slow down your workbook feel free to change them accordingly.
Hi Oscar, thank you for this article. It is really useful for me. I have just one question. Does exist a way how to update links based on this result? I have an excel where I have a lot of links but we migrated data to different place. So it means that URL address was changed. I used your macro to get all links, I updated all these to the new destination and now I am looking for a way how to update all cells when I have list of all cells where with a new link. Thank you for your tips :) Mirek
Mirek
You can use "Find & Replace" to update your cells with new hyperlinks.
1. Press CTRL + H to open the "Find & Replace" dialog box.
2. Find what: Old url
3. Replace with: New url
4. Press with left mouse button on "Replace All" button to apply changes to all cells on worksheet.
Hello Oscar,
thank you for this article. It is really useful for me. I am looking for a macro which can search hyperlinks from many excel files which are in a folder. how I should amend this macro to search for files within a folder and give me file name and hyperlink name. Thank you in advanced.