How to extract a hyperlink from an Excel cell

Question

I've searched on this and found multiple like posts, but cannot get anything to work for me.  I am a total novice when it comes to VB, but can usually muddle through when given instructions.

I'm running Mac Office 2011.

I have a worksheet (named "multiples") in the following format:

Reference	State	Multiple	Text
Number		Name	Click Me
64000005	ALABAMA	19th Century Spring Hill Neighborhood TR	Text
64000006	ALABAMA	Anniston MRA	Text

The TEXT field has a URL. I would like to extract the hyperlink from it and put it into the adjacent cell.

I found this macro:

Sub hyper()

Dim sht As Worksheet: Set sht = Worksheets("multiples")

Dim cll As Range

    For Each cll In sht.Range("e8:e15")

        sht.Cells(cll.Row, cll.Column + 1).Value = cll.Hyperlinks(1).Address

    Next cll

End Sub

When I try to run it tho, it stops at the 5th line and I get a "Run-time error '9': Subscript out of range" error and it highlights the entire line.

Could someone please help me with this.  I'm just looking for the easiest way to achieve this goal and I cannot figure it out on my own.

Many thanks for any guidance.

Answer 1

Your "Run-time error '9' happens because cll.Hyperlinks(1).Address is not a valid property, most likely a cell that doesn't have any hyperlinks.

To fix this, add an "if" statement:

        If cll.Hyperlinks.Count > 0 Then

            sht.Cells(cll.Row, cll.Column + 1).Value = cll.Hyperlinks(1).Address

        End If

Worked well for my purposes. I had 72 rows from which 50 had hyperlinks and the others were just text or blank.

Answer 2

Jim:

Thank you very much for your efforts - I appreciate it.

Yes, I really did have the range of cells selected before I ran the macro - I really did.  I just tried it again with the same result.

The really funny thing (embarrassing, really) is that the URLs were right there all along, just in HIDDEN cells!  I just didn't tumble to that until I got to looking very closely what what I had posted and realized that the cells went A, B, C, E, F and that D was missing, or rather, hidden.

So I can access the links without any macro.

Once again, thank you very much.  Next time I will try to be more observant before posting an SOS.

Answer 3

Hi Trisha,

I removed Arshad's answer because it doesn't fully address your question and because the solution he provided works only in Windows versions of Excel because the keyboard shortcuts are different. 

The macro you found doesn't work, but I found one written by Canadian Excel MVP Ken Puls on **VBA Express**that works like a charm.

Copy the code and paste it into a code module in the **VBE editor** in Excel on your Mac. Then switch to your worksheet, select the range (it must contain at least one hyperlink) then run the code named DistillHyperlinks. 

A new worksheet called Hyperlink List with your links will be made

Answer 4

Thank you so much for the code.  It works, yes, but oddly does not produce any links on the new worksheet.  I get a new worksheet, with column headings, but no data:

I think the problem is the original worksheet.  When I put the cursor on a cell that has a hyperlink, in the input area you see "=HYPERLINK(XX,"Text")":

I'm thinking maybe the actual links were on a referenced worksheet?  Only problem is, at times when the cursor hovers over the cell, I can actually see the link.

I've run out of ideas.  Any other thoughts?