how do i delete just 1 hyperlink in a column of many?

[Guest]

i am trying to write a macro to delete the hyperlink in G1; i want all other
hyperlinks in column G to remain functional. [i daily copy & paste info into
this column but don't want the title's hyperlink to be used.] when i use the
following code, it works the first time. but while testing (i'm new at vba),
when i copy a hyperlink from G2 or G10 etc., up to G1 & run my macro again,
it usually removes the hyperlink from all except the last one in the column
(i.e., it removes G1:G9 hyperlinks & leaves G10's). why is this happening?

Sub Hyperlink_Remove(
Workbooks("Fiduciaries.xls").Worksheets("Sheet4").Range("G1").Hyperlinks.Delete
End Sub

thank you for any suggestions.
elizabeth

 

[Tom Ogilvy]

Try

Sub Hyperlink_Remove()
Workbooks("Fiduciaries.xls").Worksheets("Sheet4").Range("G1").Hyperlinks(1).
Delete
End Sub

 

[Guest]

Tom:
Thank you for your suggestion. I tried it, but get the same result as my
code (it initially works, but on subsequent tries it deletes all hyperlinks
except the last one in the column). But thanks again for giving it a shot.
Elizabeth

Try

Sub Hyperlink_Remove()
Workbooks("Fiduciaries.xls").Worksheets("Sheet4").Range("G1").Hyperlinks(1).
Delete
End Sub

--
Regards,
Tom Ogilvy

i am trying to write a macro to delete the hyperlink in G1; i want all other
hyperlinks in column G to remain functional. [i daily copy & paste info into
this column but don't want the title's hyperlink to be used.] when i use the
following code, it works the first time. but while testing (i'm new at vba),
when i copy a hyperlink from G2 or G10 etc., up to G1 & run my macro again,
it usually removes the hyperlink from all except the last one in the column
(i.e., it removes G1:G9 hyperlinks & leaves G10's). why is this happening?

Sub Hyperlink_Remove()
Workbooks("Fiduciaries.xls").Worksheets("Sheet4").Range("G1").Hyperlinks.Del
ete
End Sub

thank you for any suggestions.
elizabeth

 

[Tom Ogilvy]

I can't reproduce that behavior, so I don't know what to tell you. the
reference

Workbooks("Fiduciaries.xls").Worksheets("Sheet4").Range("G1")

is pretty specific.

--
Regards,
Tom Ogilvy

Tom:
Thank you for your suggestion. I tried it, but get the same result as my
code (it initially works, but on subsequent tries it deletes all hyperlinks
except the last one in the column). But thanks again for giving it a shot.
Elizabeth

Try

Sub Hyperlink_Remove()
Workbooks("Fiduciaries.xls").Worksheets("Sheet4").Range("G1").Hyperlinks(1).
Delete
End Sub

--
Regards,
Tom Ogilvy

i am trying to write a macro to delete the hyperlink in G1; i want all other
hyperlinks in column G to remain functional. [i daily copy & paste

info
into

this column but don't want the title's hyperlink to be used.] when i

use
the

following code, it works the first time. but while testing (i'm new

at
vba),

when i copy a hyperlink from G2 or G10 etc., up to G1 & run my macro again,
it usually removes the hyperlink from all except the last one in the column
(i.e., it removes G1:G9 hyperlinks & leaves G10's). why is this happening?

Sub Hyperlink_Remove()

Workbooks("Fiduciaries.xls").Worksheets("Sheet4").Range("G1").Hyperlinks.Del
ete

End Sub

thank you for any suggestions.
elizabeth

 

[Dave Peterson]

I tried this:

I put a hyperlink in A5 (just typed www.microsoft.com)
I autofilled (by dragging) into A4:A1

Then ran this:
Option Explicit
Sub testme()
Range("a1").Hyperlinks.Delete
End Sub

And it duplicated the OP's problem.

Before I did the deletion,
?activesheet.hyperlinks.count
returned 2 (instead of one each for a1 to a5)

(sigh: Hyperlinks are strange beasts.)

I thought that if I could break that "group" of links, then I could delete the
link in A1:

Option Explicit
Sub testme()
With Range("a1")
If .Hyperlinks.Count > 0 Then
.Hyperlinks.Add anchor:=.Cells, Address:="www.dummy.com"
End If
.Hyperlinks.Delete
End With
End Sub

but it didn't work.

But this did:

Option Explicit
Sub testme2()
Dim myFormula As String
With Range("a1")
myFormula = .Formula
.Clear
.Formula = myFormula
End With
End Sub

To the original poster: You'll have to keep track of everything you
want--formatting (numberformat, font, boldness, underlining, borders) and then
reapply them after you clear the cell.

Them hyperlink thingies is ugly!


Ahhhh. This worked better.

Option Explicit
Sub testme3()
Dim dummyCell As Range
Set dummyCell = ActiveSheet.Cells _
.SpecialCells(xlCellTypeLastCell).Offset(1, 1)
With dummyCell
Range("a1").Copy _
Destination:=.Cells
.Hyperlinks.Delete
.Copy _
Destination:=Range("a1")
.Clear
End With
End Sub

It copies the cell to clear to the cell to the bottom right of the last used
cell. Does the work and pastes it back.

Maybe the idea will work ok for you, too.

Tom:
Thank you for your suggestion. I tried it, but get the same result as my
code (it initially works, but on subsequent tries it deletes all hyperlinks
except the last one in the column). But thanks again for giving it a shot.
Elizabeth

Try

Sub Hyperlink_Remove()
Workbooks("Fiduciaries.xls").Worksheets("Sheet4").Range("G1").Hyperlinks(1).
Delete
End Sub

--
Regards,
Tom Ogilvy

i am trying to write a macro to delete the hyperlink in G1; i want all other
hyperlinks in column G to remain functional. [i daily copy & paste info into
this column but don't want the title's hyperlink to be used.] when i use the
following code, it works the first time. but while testing (i'm new at vba),
when i copy a hyperlink from G2 or G10 etc., up to G1 & run my macro again,
it usually removes the hyperlink from all except the last one in the column
(i.e., it removes G1:G9 hyperlinks & leaves G10's). why is this happening?

Sub Hyperlink_Remove()
Workbooks("Fiduciaries.xls").Worksheets("Sheet4").Range("G1").Hyperlinks.Del
ete
End Sub

thank you for any suggestions.
elizabeth

 

[okaizawa]

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

--
HTH,

okaizawa

Tom:
Thank you for your suggestion. I tried it, but get the same result as my
code (it initially works, but on subsequent tries it deletes all hyperlinks
except the last one in the column). But thanks again for giving it a shot.
Elizabeth

:

Try

Sub Hyperlink_Remove()
Workbooks("Fiduciaries.xls").Worksheets("Sheet4").Range("G1").Hyperlinks(1).
Delete
End Sub

--
Regards,
Tom Ogilvy

i am trying to write a macro to delete the hyperlink in G1; i want all
other

hyperlinks in column G to remain functional. [i daily copy & paste info
into

this column but don't want the title's hyperlink to be used.] when i use
the

following code, it works the first time. but while testing (i'm new at
vba),

when i copy a hyperlink from G2 or G10 etc., up to G1 & run my macro
again,

it usually removes the hyperlink from all except the last one in the
column

(i.e., it removes G1:G9 hyperlinks & leaves G10's). why is this
happening?

Sub Hyperlink_Remove()

Workbooks("Fiduciaries.xls").Worksheets("Sheet4").Range("G1").Hyperlinks.Del
ete

End Sub

thank you for any suggestions.
elizabeth

 

[okaizawa]

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

 

[Guest]

Dave:
When I tried your testme3 code, it still removed the hyperlink from all
except the last one in the column, but your code maintained the blue color of
the cells it removed the hyperlinks from (except the first one - G1), as if
they were still hyperlinks. Thank you so much for your persistence in trying
to make this work. Your response told me that it wasn't as easy of a task as
I thought it would / should be, which decreased my frustration level.
okaizawa provided a short piece of code which seems to consistently work.
I'm thrilled to have this problem resolved. Thanks again!
Elizabeth

I tried this:

I put a hyperlink in A5 (just typed www.microsoft.com)
I autofilled (by dragging) into A4:A1

Then ran this:
Option Explicit
Sub testme()
Range("a1").Hyperlinks.Delete
End Sub

And it duplicated the OP's problem.

Before I did the deletion,
?activesheet.hyperlinks.count
returned 2 (instead of one each for a1 to a5)

(sigh: Hyperlinks are strange beasts.)

I thought that if I could break that "group" of links, then I could delete the
link in A1:

Option Explicit
Sub testme()
With Range("a1")
If .Hyperlinks.Count > 0 Then
.Hyperlinks.Add anchor:=.Cells, Address:="www.dummy.com"
End If
.Hyperlinks.Delete
End With
End Sub

but it didn't work.

But this did:

Option Explicit
Sub testme2()
Dim myFormula As String
With Range("a1")
myFormula = .Formula
.Clear
.Formula = myFormula
End With
End Sub

To the original poster: You'll have to keep track of everything you
want--formatting (numberformat, font, boldness, underlining, borders) and then
reapply them after you clear the cell.

Them hyperlink thingies is ugly!


Ahhhh. This worked better.

Option Explicit
Sub testme3()
Dim dummyCell As Range
Set dummyCell = ActiveSheet.Cells _
.SpecialCells(xlCellTypeLastCell).Offset(1, 1)
With dummyCell
Range("a1").Copy _
Destination:=.Cells
.Hyperlinks.Delete
.Copy _
Destination:=Range("a1")
.Clear
End With
End Sub

It copies the cell to clear to the cell to the bottom right of the last used
cell. Does the work and pastes it back.

Maybe the idea will work ok for you, too.

Tom:
Thank you for your suggestion. I tried it, but get the same result as my
code (it initially works, but on subsequent tries it deletes all hyperlinks
except the last one in the column). But thanks again for giving it a shot.
Elizabeth

Try

Sub Hyperlink_Remove()
Workbooks("Fiduciaries.xls").Worksheets("Sheet4").Range("G1").Hyperlinks(1).
Delete
End Sub

--
Regards,
Tom Ogilvy


i am trying to write a macro to delete the hyperlink in G1; i want all
other
hyperlinks in column G to remain functional. [i daily copy & paste info
into
this column but don't want the title's hyperlink to be used.] when i use
the
following code, it works the first time. but while testing (i'm new at
vba),
when i copy a hyperlink from G2 or G10 etc., up to G1 & run my macro
again,
it usually removes the hyperlink from all except the last one in the
column
(i.e., it removes G1:G9 hyperlinks & leaves G10's). why is this
happening?

Sub Hyperlink_Remove()

Workbooks("Fiduciaries.xls").Worksheets("Sheet4").Range("G1").Hyperlinks.Del
ete
End Sub

thank you for any suggestions.
elizabeth

 

[Guest]

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

 

[Guest]

okaizawa:
oops - i didn't realize Test was "calling" Delete_Hyperlink until i deleted
that part of the code & it didn't work. your solution still works, i just
misspoke when i said i only needed the Test part (which you already knew).
thanks again!
elizabeth

 

[Dave Peterson]

I didn't notice this. But I see you have a solution that works.

Dave:
When I tried your testme3 code, it still removed the hyperlink from all
except the last one in the column, but your code maintained the blue color of
the cells it removed the hyperlinks from (except the first one - G1), as if
they were still hyperlinks. Thank you so much for your persistence in trying
to make this work. Your response told me that it wasn't as easy of a task as
I thought it would / should be, which decreased my frustration level.
okaizawa provided a short piece of code which seems to consistently work.
I'm thrilled to have this problem resolved. Thanks again!
Elizabeth

I tried this:

I put a hyperlink in A5 (just typed www.microsoft.com)
I autofilled (by dragging) into A4:A1

Then ran this:
Option Explicit
Sub testme()
Range("a1").Hyperlinks.Delete
End Sub

And it duplicated the OP's problem.

Before I did the deletion,
?activesheet.hyperlinks.count
returned 2 (instead of one each for a1 to a5)

(sigh: Hyperlinks are strange beasts.)

I thought that if I could break that "group" of links, then I could delete the
link in A1:

Option Explicit
Sub testme()
With Range("a1")
If .Hyperlinks.Count > 0 Then
.Hyperlinks.Add anchor:=.Cells, Address:="www.dummy.com"
End If
.Hyperlinks.Delete
End With
End Sub

but it didn't work.

But this did:

Option Explicit
Sub testme2()
Dim myFormula As String
With Range("a1")
myFormula = .Formula
.Clear
.Formula = myFormula
End With
End Sub

To the original poster: You'll have to keep track of everything you
want--formatting (numberformat, font, boldness, underlining, borders) and then
reapply them after you clear the cell.

Them hyperlink thingies is ugly!


Ahhhh. This worked better.

Option Explicit
Sub testme3()
Dim dummyCell As Range
Set dummyCell = ActiveSheet.Cells _
.SpecialCells(xlCellTypeLastCell).Offset(1, 1)
With dummyCell
Range("a1").Copy _
Destination:=.Cells
.Hyperlinks.Delete
.Copy _
Destination:=Range("a1")
.Clear
End With
End Sub

It copies the cell to clear to the cell to the bottom right of the last used
cell. Does the work and pastes it back.

Maybe the idea will work ok for you, too.

Tom:
Thank you for your suggestion. I tried it, but get the same result as my
code (it initially works, but on subsequent tries it deletes all hyperlinks
except the last one in the column). But thanks again for giving it a shot.
Elizabeth

:

Try

Sub Hyperlink_Remove()
Workbooks("Fiduciaries.xls").Worksheets("Sheet4").Range("G1").Hyperlinks(1).
Delete
End Sub

--
Regards,
Tom Ogilvy


i am trying to write a macro to delete the hyperlink in G1; i want all
other
hyperlinks in column G to remain functional. [i daily copy & paste info
into
this column but don't want the title's hyperlink to be used.] when i use
the
following code, it works the first time. but while testing (i'm new at
vba),
when i copy a hyperlink from G2 or G10 etc., up to G1 & run my macro
again,
it usually removes the hyperlink from all except the last one in the
column
(i.e., it removes G1:G9 hyperlinks & leaves G10's). why is this
happening?

Sub Hyperlink_Remove()

Workbooks("Fiduciaries.xls").Worksheets("Sheet4").Range("G1").Hyperlinks.Del
ete
End Sub

thank you for any suggestions.
elizabeth