Results 1 to 4 of 4
  • Thread Tools
  1. 3 Star Lounger
    Join Date
    Jul 2001
    Location
    San Jose, California, USA
    Posts
    290
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Copying hyperlinks (2003 SP2)

    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.

  2. Subscribe to our Windows Secrets Newsletter - It's Free!

    Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 13 Times in 13 Posts

    Re: Copying hyperlinks (2003 SP2)

    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.

  4. 3 Star Lounger
    Join Date
    Jul 2001
    Location
    San Jose, California, USA
    Posts
    290
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copying hyperlinks (2003 SP2)

    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?

  5. Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 13 Times in 13 Posts

    Re: Copying hyperlinks (2003 SP2)

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •