Solved: Removing hyperlinks

[Tenspeed39355]

Good morning. I have a problem with removing hyperlinks from a ss
There are times that one or two columns will have hyperlinks in them. When I run the mouse across the ss the hand will come up and then the white cross will come up on another column. Is there a way to remove the hyperlinks from the problem columns? Thanks for your time with this.
Max

[johnske]

Hi Max,

Cells.Hyperlinks.Delete for the whole w/sheet or Selection.Hyperlinks.Delete... etc

[snicho]

(This is my first posting, so I apologise in advance if there are formatting problems.)

Not sure if you want to go to this extent, however, something along these lines provides you a little more flexibility:



[vba]
Public Sub DeleteBrokenHyperlinks()
Dim test As Boolean
Dim count As Integer
count = 0

test = True
On Error GoTo OnError


For Each h In ActiveSheet.Hyperlinks

If Dir(h.Address) = "" Then

MsgBox h.Name & vbCr & _
h.Address & vbCr & _
h.SubAddress & vbCr & _
"in cell " & h.Range.Address & vbCr & _
"link path does not exist"

test = False
response = MsgBox("Do you want to delete this link?", vbYesNo)

If response = vbYes Then

Range(h.Range.Address).Hyperlinks(1).Delete
Application.StatusBar = False
Exit Sub

End If

End If
count = count + 1
Application.StatusBar = "Links checked: " & count
Next

If test Then MsgBox "All Hyperlinks test ok"
Application.StatusBar = False
Exit Sub
OnError:
MsgBox ("Error with: " & h.Name & vbCr & "File not found.")
Application.StatusBar = False
End Sub
[/vba]

[Tenspeed39355]

To JOHNSKE I hate to do this but I do not understand your reply.
As to cells,hyperlink where do you want me to start. Once I find the starting point I think I can run with the rest.
Max

[johnske]

To JOHNSKE I hate to do this but I do not understand your reply.
As to cells,hyperlink where do you want me to start. Once I find the starting point I think I can run with the rest.
Max

Hi Max,

Here's some examples[VBA]Option Explicit

Sub RemoveSheetLinks()
' 'to remove all hyperlinks from a sheet
Cells.Hyperlinks.Delete
End Sub

Sub RemoveSelectionLinks()
' 'to remove all hyperlinks from a selection
Selection.Hyperlinks.Delete
End Sub

Sub RemoveColumnLinks()
' 'to remove all hyperlinks from a column
Columns(1).Hyperlinks.Delete
End Sub

Sub RemoveRowLinks()
' 'to remove all hyperlinks from a row
Rows(1).Hyperlinks.Delete
End Sub

Sub RemoveRangeLinks()
' 'to remove all hyperlinks from a given range
Range("A1:Z30").Hyperlinks.Delete
End Sub[/VBA]

[Tenspeed39355]

Johnske The examples you gave me on removing the hyperlinks works like
a charm. Thank you very much. You guys are great in helping with problems. Keep up the good work.
Max I will give ratings.

[johnske]

Not a prob Max - thanx