How do I copy hyperlink only (and not text) to another cell?

Question

I have a spreadsheet where column A displays names. There are a few hundred names and each has a different hyperlink (which links to that person's web page). I want to transfer those hyperlinks across to a different column which has different text in and no hyperlinks.

Not every cell in column A has a hyperlink. There are groups of cells merged together, so A2:A7 has one link, A8:A13 the next, A9:10 the next (i.e. number of cells merged is not uniform).

e.g. where A2:A7 reads "Bob" and links to www.bob.com, I want I2:I7, which reads, "Smith," and does not link to anything, to link to www.bob.com. I want to do this repeatedly, copying links from A2:A579 into I2:I579.

The information is copied from a table within a web page, and that is where the hyperlinks come from.

Solution

This macro will help

Sub SwapIt()

For i = 2 To 579

If Range("A" & i).Value <> "" And Range("I" & i).Value <> "" Then

    Dim newLink As String ' the new link string needs a place to live... just like me!

    If Range("A" & i).Hyperlinks.Count = 1 Then
    newLink = Range("A" & i).Hyperlinks(1).Address ' Get the old horrible link :)
    Range("I" & i).Hyperlinks.Add anchor:=Range("I" & i), Address:=Range("I" & i) ' horrible hack, just to get it to a link
    Range("I" & i).Hyperlinks(1).Address = newLink '' replace with the new link... Much better. Like a ray of sunshine
    End If
End If

Next i

End Sub

This will move the hyperlink From A2 to I2, then A3 to I3 etc

Comments

[CharlieRB]

Here is an important piece of information you've left out of your question; How are the existing hyperlinks created in the cells? Do they use the HYPERLINK function or are they embedded using Insert Hyperlink from the Insert ribbon?

[Dave]

This question is so unclear... Have the information is in comments. Why explain the order was incorrect if you've fixed it, it just adds waffle to your question... Please click here to edit your question and explain exactly how the information is populated in your Excel document. As the question is now, IMO it's unclear what you want and I have voted to close the question

[OfficeLackey]

@DaveRook I'm sorry, this is the first time I have used this forum and I didn't think to edit the question rather than comment. I'm doing my best to present the information such that people can help me. Hope this is better. Please tell me what you need to know if I haven't expressed myself clearly enough still.

[Dave]

Right, your question is now clear. However, I suspect you will want to do this multiple times? If that is the case, then what is the logic? Do you always want to remove A1 hyperlink and replace it with A10 hyperlink? And then again with B1 and B10, C1 and C10 etc?

[OfficeLackey]

@DaveRook Please see updated question.

[OfficeLackey]

@Madball73 I had already looked at that question and it is not the same as mine - I have since revised the phrasing of my question to clarify

[OfficeLackey]

@CharlieRB See edit

[blakemade]

The tagged duplicate question is asking for an excel formula. Here is an answer to this original question: 1) select and copy cells with hyperlinked text 2) open Outlook and paste text into draft email 3) change formatting from "HTML" to plain text 4) see text links convert to full URLs 5) copy these back into Excel

[Dave]

Updated the code, this will now do from row 2 to 579

[OfficeLackey]

I am getting: Run time error 9 - subscript out of range.

[OfficeLackey]

Range("I" & i).Hyperlinks(1).Address = newLink

[OfficeLackey]

The I column does not have any hyperlinks originally (which I have stated in question edit) - does this make a difference to your code?

[OfficeLackey]

No, my original question said each has different text and that I want to move the hyperlinks from A to I. I realised that I had not explicitly stated there were no hyperlinks in I, and updated question appropriately. Sorry if I am being unhelpful - I am doing my best to explain my problem succinctly!

[Dave]

Updated - I've had to hack it in due to time restraints, but, I will still work

[OfficeLackey]

Now getting same Run time error on: newLink = Range("A" & i).Hyperlinks(1).Address - sorry to be a pain again =S

[OfficeLackey]

Ah! No. That would be the issue. Will edit question. Sorry.

[Dave]

Updated again for you. OfficeLackey, if you're having to edit your question this often, you may need to consider asking a new question. You can ask many questions. The idea isn't to ask 1 question, and keep updating the question when you realise something isn't quite right etc

[OfficeLackey]

See edit - I am aware this is rather complicated and I'm probably not helping. Very grateful for your time.

[Dave]

Can you confirm if it works or not.