Home |
Search |
Today's Posts |
#1
|
|||
|
|||
sorting links
Please, someone have a macro for sorting hyperlinks? When I sort a selected area with hyperlinks the resulting hyperlinks are disabled or mixed-up. I can send an example of the file. Thanks. Branko |
#2
|
|||
|
|||
In previous tests I have not reproduced a problem of hyperlinks not sorting with the cells containing them. I would be interested in seeing the file ( Bill_Manville @ compuserve.com , without the spaces) Which version of Excel are you using? A fix would be to use the HYPERLINK worksheet function to create your links. The following macro should change all hyperlinks in a selected range into HYPERLINK function calls Sub MakeHyperlinkFunctions() Dim C As Range Dim H As Hyperlink Dim stDest As String For Each C In Selection.Cells If C.Hyperlinks.Count 0 Then Set H = C.Hyperlinks(1) stDest = H.Address If H.SubAddress < "" Then stDest = stDest & "#" & H.SubAddress If C.HasFormula Then If Left(C.Formula, 11) < "=HYPERLINK(" Then C.Formula = "=HYPERLINK(""" & stDest & """," & Mid(C.Formula, 2) & ")" End If Else C.Formula = "=HYPERLINK(""" & stDest & """,""" & C.Text & """)" End If End If Next End Sub Bill Manville MVP - Microsoft Excel, Oxford, England No email replies please - respond to newsgroup |
#3
|
|||
|
|||
FWIW the problem did not reproduce when sent to me by email
Bill Manville MVP - Microsoft Excel, Oxford, England No email replies please - respond to newsgroup |
#4
|
|||
|
|||
Further for the record.
Branko sent me a reproduceable example. I discovered that some cells had 2 hyperlinks attached and that some of the hyperlinks were associated with multiple cells. I wrote a macro to resolve those issues, replacing multi-cell hyperlinks with single cell ones and, where 2 hyperlinks had been in a cell putting the second hyperlink in a spare cell to the right so that Branko could decide which one to keep. The macro went like this: Sub FixHyperlinks() Dim C As Range Dim H As Hyperlink Dim R As Range Dim C2 As Range Dim I As Integer Dim stDest As String For Each C In Selection.Cells For I = C.Hyperlinks.Count To 1 Step -1 Set H = C.Hyperlinks(I) Set R = H.Range If R.Address < C.Address Then ' we have a multiple cell hyperlink - note the destination stDest = H.Address If H.SubAddress < "" Then stDest = stDest & "#" & H.SubAddress H.Delete Debug.Print "Deleted hyperlink from " & R.Address For Each C2 In R.Cells If C2.Hyperlinks.Count = 0 Then ' we removed the hyperlink from this cell C2.Hyperlinks.Add C2, stDest, , , C2.Text Debug.Print "Added hyperlink to " & C2.Address Else ' put the removed hyperlink 2 cells to right C2.Offset(, 2).Hyperlinks.Add C2.Offset(, 2), stDest, , , C2.Text End If C2.Style = "Hyperlink" Next End If Next Next End Sub Bill Manville MVP - Microsoft Excel, Oxford, England No email replies please - respond to newsgroup |
#5
|
|||
|
|||
Bill! Thank you very much, your code solved this difficult problem. Best regards, Branko "Bill Manville" wrote: Further for the record. Branko sent me a reproduceable example. I discovered that some cells had 2 hyperlinks attached and that some of the hyperlinks were associated with multiple cells. I wrote a macro to resolve those issues, replacing multi-cell hyperlinks with single cell ones and, where 2 hyperlinks had been in a cell putting the second hyperlink in a spare cell to the right so that Branko could decide which one to keep. The macro went like this: Sub FixHyperlinks() Dim C As Range Dim H As Hyperlink Dim R As Range Dim C2 As Range Dim I As Integer Dim stDest As String For Each C In Selection.Cells For I = C.Hyperlinks.Count To 1 Step -1 Set H = C.Hyperlinks(I) Set R = H.Range If R.Address < C.Address Then ' we have a multiple cell hyperlink - note the destination stDest = H.Address If H.SubAddress < "" Then stDest = stDest & "#" & H.SubAddress H.Delete Debug.Print "Deleted hyperlink from " & R.Address For Each C2 In R.Cells If C2.Hyperlinks.Count = 0 Then ' we removed the hyperlink from this cell C2.Hyperlinks.Add C2, stDest, , , C2.Text Debug.Print "Added hyperlink to " & C2.Address Else ' put the removed hyperlink 2 cells to right C2.Offset(, 2).Hyperlinks.Add C2.Offset(, 2), stDest, , , C2.Text End If C2.Style = "Hyperlink" Next End If Next Next End Sub Bill Manville MVP - Microsoft Excel, Oxford, England No email replies please - respond to newsgroup |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Phantom links | Excel Discussion (Misc queries) | |||
Links to other files | Excel Discussion (Misc queries) | |||
Manually update links | Excel Worksheet Functions | |||
HELP!!! Missing Links!!! | New Users to Excel | |||
Links in formulas change when another user runs a workbook | Excel Discussion (Misc queries) |