Sorting a list with a hyperlink field (XL2000)

Topic

New Reply

I have a long (11,000+ entries) data list in Excel with a column of hyperlinks. The hyperlinks are URLs attached to a text entry, not just the URL itself. I want to sort the list on a text field. When I do this the hyperlink field APPEARS to have been sorted properly but it seems that it is only the text that has been sorted – the hyperlinks remain fixed to the cell where they started. This results in a list that is sorted but the text is pointed to the wrong URL!! Help!!

Paul

Reply | Quote

Replies

The way to imagine is that the “hyperlink object” (the hyperlink itself) is attached to the cell itself. When you sort, the text inside the cell is sorted. The cells are not physically sorted, just the “contents” of the cells. Thus the attached objects stay where they are.

I think the best way around this is to use the HYPERLINK function. The function can be linked to a cell contents so when the cells are sorted the function will sort along with the contents and thus the link will also be sorted.

=HYPERLINK(URL,Display)

Steve

Reply | Quote

Steve,
Thanks for that. Is there any way I can automate the entry of this function into a new column or do I need to go thru it by hand ? I have some familiarity with VBA but I am not having any success in extracting the URL from a cell. Any help ?

Paul

Reply | Quote

Try this on a backup copy. It will change all hyperlinks in the worksheet to the function using the current link as the “URL” and the current value as the display value. It will then delete the hyperlink, leaving the function.

Steve

Sub TransformHL()
    Dim HL As Hyperlink
    Dim rCell As Range
    For Each HL In ActiveSheet.Hyperlinks
        With HL
            Set rCell = .Range
            rCell.FormulaR1C1 = "=Hyperlink(" & _
                Chr(34) & .Name & Chr(34) & ", " & _
                Chr(34) & rCell.Value & Chr(34) & ")"
            .Delete
        End With
    Next
End Sub

Reply | Quote

Many thanks for your help!!

Paul

Reply | Quote

Many thanks for your help!!

Paul

Reply | Quote

Try this on a backup copy. It will change all hyperlinks in the worksheet to the function using the current link as the “URL” and the current value as the display value. It will then delete the hyperlink, leaving the function.

Steve

Sub TransformHL()
    Dim HL As Hyperlink
    Dim rCell As Range
    For Each HL In ActiveSheet.Hyperlinks
        With HL
            Set rCell = .Range
            rCell.FormulaR1C1 = "=Hyperlink(" & _
                Chr(34) & .Name & Chr(34) & ", " & _
                Chr(34) & rCell.Value & Chr(34) & ")"
            .Delete
        End With
    Next
End Sub

Reply | Quote

Steve,
Thanks for that. Is there any way I can automate the entry of this function into a new column or do I need to go thru it by hand ? I have some familiarity with VBA but I am not having any success in extracting the URL from a cell. Any help ?

Paul

Reply | Quote