Copying hyperlinks (2003 SP2)

[asimpkins]

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). 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>post 536236</post#>) 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.

[HansV]

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.

[asimpkins]

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?

[HansV]

It's not <code>X1UP</code> but <code>XLUP</code>. 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.