ikaizawa:

sorry for the delay in responding; a shoulder injury put me out of
commission for a week. i am so happy to return & find that the code you
suggested works. i only used the following & it consistently removes the
hyperlink from only G1, leaving hyperlinks in the rest of the column.
Sub Test()
Delete_HyperLink Range("G1")
End Sub

i want to study all of the code that you & dave provided, to learn from it
for future uses, but am thrilled that my current issue is behind me. thank
you so much!

elizabeth


"okaizawa" wrote:

i made some code that deletes and adds hyperlink in a cell.

Sub Test()
Delete_HyperLink Range("G1")
End Sub

Sub Delete_HyperLink(Cell As Range)
Dim HL As HyperLink
Dim r As Range, c As Range
Dim adr As String, sub_adr As String, scr_tip As String

For Each HL In Cell.Hyperlinks
Set r = HL.Range
adr = HL.Address
sub_adr = HL.SubAddress
scr_tip = HL.ScreenTip
HL.Delete
For Each c In r
If c.Address < Cell.Address Then
With c.Hyperlinks.Add(Anchor:=c, Address:=adr)
.SubAddress = sub_adr
.ScreenTip = scr_tip
End With
End If
Next
Next
End Sub

--
HTH,

okaizawa


okaizawa wrote:
Hi,

it seems that 'HyperLink' belongs to a sheet, not a cell.
pasting to cell-range makes one new hyperlink for the range that is
its anchor, not for each cell.
i suppose that you should add the hyperlink again with new anchor-range,
or have made one hyperlink have one anchor-cell at first.

anchor-range can be known from Range property of HyperLink object.

Sub Test()
Dim h, r, r2
On Error Resume Next
For Each h In ActiveSheet.Hyperlinks
Set r = Nothing
Set r = h.Range
Err.Clear
If Not r Is Nothing Then
For Each r2 In r
Debug.Print r2.Address(0, 0) & ": ", _
r2.Hyperlinks(1).Range.Address(0, 0)
Next
End If
Next
End Sub