Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
how do i delete just 1 hyperlink in a column of many?
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
how do i delete just 1 hyperlink in a column of many?
Try
Sub Hyperlink_Remove() Workbooks("Fiduciaries.xls").Worksheets("Sheet4"). Range("G1").Hyperlinks(1). Delete End Sub -- Regards, Tom Ogilvy "Elizabeth" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
how do i delete just 1 hyperlink in a column of many?
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 "Tom Ogilvy" wrote: Try Sub Hyperlink_Remove() Workbooks("Fiduciaries.xls").Worksheets("Sheet4"). Range("G1").Hyperlinks(1). Delete End Sub -- Regards, Tom Ogilvy "Elizabeth" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
how do i delete just 1 hyperlink in a column of many?
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 "Elizabeth" wrote in message ... 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 "Tom Ogilvy" wrote: Try Sub Hyperlink_Remove() Workbooks("Fiduciaries.xls").Worksheets("Sheet4"). Range("G1").Hyperlinks(1). Delete End Sub -- Regards, Tom Ogilvy "Elizabeth" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
how do i delete just 1 hyperlink in a column of many?
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. Elizabeth wrote: 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 "Tom Ogilvy" wrote: Try Sub Hyperlink_Remove() Workbooks("Fiduciaries.xls").Worksheets("Sheet4"). Range("G1").Hyperlinks(1). Delete End Sub -- Regards, Tom Ogilvy "Elizabeth" wrote in message ... 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
how do i delete just 1 hyperlink in a column of many?
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 Elizabeth wrote: 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 "Tom Ogilvy" wrote: Try Sub Hyperlink_Remove() Workbooks("Fiduciaries.xls").Worksheets("Sheet4" ).Range("G1").Hyperlinks(1). Delete End Sub -- Regards, Tom Ogilvy "Elizabeth" wrote in message ... 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
how do i delete just 1 hyperlink in a column of many?
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
how do i delete just 1 hyperlink in a column of many?
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 "Dave Peterson" wrote: 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. Elizabeth wrote: 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 "Tom Ogilvy" wrote: Try Sub Hyperlink_Remove() Workbooks("Fiduciaries.xls").Worksheets("Sheet4"). Range("G1").Hyperlinks(1). Delete End Sub -- Regards, Tom Ogilvy "Elizabeth" wrote in message ... 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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
how do i delete just 1 hyperlink in a column of many?
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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
how do i delete just 1 hyperlink in a column of many?
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 "Elizabeth" wrote: 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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
how do i delete just 1 hyperlink in a column of many?
I didn't notice this. But I see you have a solution that works.
Elizabeth wrote: 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 "Dave Peterson" wrote: 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. Elizabeth wrote: 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 "Tom Ogilvy" wrote: Try Sub Hyperlink_Remove() Workbooks("Fiduciaries.xls").Worksheets("Sheet4"). Range("G1").Hyperlinks(1). Delete End Sub -- Regards, Tom Ogilvy "Elizabeth" wrote in message ... 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 -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy column header to next column, delete & delete every nth colum | New Users to Excel | |||
How do I delete a Hyperlink in a cell | Excel Worksheet Functions | |||
Keep hyperlink, delete graphic | Excel Discussion (Misc queries) | |||
Hyperlink Delete problem | Excel Programming | |||
Delete items in Edit Hyperlink window | Excel Discussion (Misc queries) |