Copying hyperlinks (2003 SP2)

Topic

New Reply

I have a worksheet in which every cell in one of its columns contains a movie title and also has a hyperlink to information about the movie in the Internet Movie Database (http://www.imdb.com[/url%5D). I want to sort the sheet according to a formula or function based on that cell, rather than the actual data in it, so I put the formula in a new cell and copy it down the length of the sheet and then sort it. This part works (thanks to Hans, post 536236) but I want to hide the original title column so that only the formula result is visible. How do I get the hyperlink to follow the formula result into the second column? I can’t find an option to copy and paste just the hyperlink without also copying the cell’s contents, and thereby overwriting the formula.

Reply | Quote

Replies

The following macro will transfer hyperlinks from column A to column B, starting in row 1 and continuing until the last filled cell in column A. Adapt as needed.

Sub TransferHyperlinks()
Dim lngMaxRow As Long
Dim lngRow As Long
Dim wsh As Worksheet
Set wsh = ActiveSheet
lngMaxRow = Range(“A65536”).End(xlUp).Row
For lngRow = 1 To lngMaxRow
wsh.Hyperlinks.Add Range(“B” & lngRow), _
Range(“A” & lngRow).Hyperlinks(1).Address
Range(“A” & lngRow).Hyperlinks.Delete
Next lngRow
End Sub

The line to delete the hyperlink from column A is optional.

Reply | Quote

Thanks Hans. I can understand the logic here, except that I’m not quite sure how the line “lngMaxRow = Range(“A65536″).End(xlUp).Row” works. It starts at the last row and presumably goes up to obtain the row number of the first row in which it finds data? What does “x1UP” mean?

Reply | Quote

It’s not X1UP but XLUP. End(xlUp) is the VBA equivalent of pressing the End key followed by the Up arrow key. Starting from the bottommost cell in column A, this finds the last non-blank cell in column A.

Reply | Quote