List all hyperlinks in worksheet
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.
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?
- Press Alt +F11
- Insert a module
- Paste vba code to code window
- Exit VB Editor Alt + Q
How to start macro?
- Press Alt+F8
- Click "ListHyperlinks"
- Click "Run" button
What happens when I run the macro?
- New sheet is inserted
- 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
Tip! If you want to stop automatic hyperlinking (excel 2010) follow these steps
- Click "File" on the ribbon
- Click "Options"
- Click "Proofing"
- Click "AutoCorrect Options..."
- Go to "Autoformat as you type" on the menu
- Disable "Internet and network paths with hyperlinks"
Create links to all sheets in a workbook
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 […]
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 […]
Navigate to first empty cell using a hyperlink formula
This article will demonstrate how to create a hyperlink that takes you to the first empty cell below data in […]
Hide specific worksheets programmatically
This article demonstrates techniques to hide and unhide worksheets programmatically. The image above shows the Excel window and the worksheet […]
8 Responses to “List all hyperlinks in worksheet”
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+Click the tabs for the sheets you want... and if you want all the sheets, select the first sheet and then Shift+Click the last sheet. After you have selected the sheets you want to process, then run this macro.
Sorry, 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...
Rick 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. Click "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.