Deleting Hyperlinks

[Pauljj]

I have come across many posts but none that simply do the following

I have for some erroneous reason, hundreds of random hyperlinks all over a sheet. I need to remove the links whilst keeping everything else in the cell in place, ie format, value etc etc

Any ideas please ?

 

[Comfy]

I use:

Sub ZapHyperlinks()
    Cells.Hyperlinks.Delete
End Sub

But I'm not sure that's what your after.

 

[JazzSP8]

Hi

If you want to delete all the hyperlinks in a worksheet then;

    Sub RemoveHyperlinks()
        ActiveSheet.Hyperlinks.Delete
    End Sub

Should do it

 

[Pauljj]

Thank you for your replies, I tried both methods and got the following error message, then excel crashed

Run time error '-2147417848(80010108)':
Method Delete' of object' hyperlinks failed

 

[JazzSP8]

How odd

In your original post you suggested that your hyperlinks were erroneous and you needed to keep formatting?

Do you have an example of that data? How do you get that data into Excel?

Something in that might be a problem.

(I might not be able to get back to you today but the bump / info might do you some good)

 

[Pauljj]

This is part of the text. The hyperlinks appeared in error, no one seems to know how they got there and now we just can't seem to get rid of them without doing each cell manually

2c GPD roster.xls
	A	B	C	D	E	F	G	H	I	J
15			JoanneA	X	P	1	1	1	1	X
16			SamSn	X	1	1	1	1	1	X
17			SarahM	X	1	1	1	1	1	X
18			MariaD	X	1	1	1	1	1	X
19			MattR	X	1	1	1	1	1	X
20			<>	<>	<>	<>	<>	<>	<>	<>
21			CarmenH
22			AlanW	X	1	1	1	1	1	X
23		<>	<>	<>	<>	<>	<>	<>	<>	<>
24		<>	<>	<>	<>	<>	<>	<>	<>	<>
25		PT	Grainne4	X	X	1	1	1	X	X
26		PT	Alda1	X	1	1	1	X	X	X
27
28
29
Jul-Dec

 

[JazzSP8]

So the "<>" are the cells with the hyperlinks?

Where do they link to?

Have you tried recording a macro when your deleting them and amending it to do the whole workshet? (If not then try recording it and posting back what you get)

 

[Pauljj]

Im amazed at how difficult this seems to be, recorded the macro to remove 1 link, it took the formatting away. So copied the formatting elsewhere, so I could copy it back then had this macro


Selection.Hyperlinks(1).Delete


Tried to then select the whole area and run the macro, literally nothing happened then tried again then I go

Run time error 9, subscript out of range ??????

So puzzled

 

[Comfy]

Try:

For Each h in Worksheets(1).Hyperlinks
    h.delete
Next

 

[Pauljj]

I get the same error message as earlier which is

Run time error '-2147417848(80010108)':
Method Delete' of object' hyperlinks failed