Deleting multiple hyperlinks

[Guest]

i am currently having problems with hyperlinks. Is there a way to
permanently remove from a spreadsheet without having to go into each cell
individually (I have text in the cells)? I have been deleting them and they
keep reappearing - the hyperlinks are even appearing in new spreadsheets and
now seem to be embedded in other spreadsheets.

Any help would be much appreciated.

 

[Guest]

Hi Tamsin,

To delete a selection of hyperlinks, select the required area of cells and
then do the following:
- Press Alt + F11 to open the Visual Basic Editor.
- Press Ctrl + G to view the 'Immediate Window'.
- Type selection.hyperlinks.delete into the immediate window and press return.
- Close the immediate window and VB editor - you should see that your links
have been removed.
- You could create a VBA sub (macro) to do this and put it in a tool bar or
menu if you wanted.

To prevent Excel from automatically converting text into hyperlinks, do the
following:
- From the menu, select Tools, Options, then the Spelling tab.
- Click the 'Autocorrect Options...' button, then the 'Autoformat As You
Type' tab.
- Un-tick the box entitled 'Internet and Network Paths with Hyperlinks'.

You should beware that this is a global setting so hyperlinks will not be
automatically created on any workbooks until you turn them back on.

Hope this helps,

Mark

 

[Gord Dibben]

Tamsin

To remove all hyperlinks at once.

Sub removeAllLinksFromSelection()
Selection.Hyperlinks.Delete
End Sub

As far as hyperlinks appearing in new workbooks I would suspect that your
default new workbook or new worksheet is based upon a Template saved with the
links.

Look for a BOOK.xlt or SHEET.xlt in your XLSTART folder.

If found, move out of that folder.


Gord Dibben Excel MVP

 

[Guest]

There is another way (from excel help!!):
To Deactivate several hyperlinks at once:
1. Type the number 1 in a blank cell, and right-click the cell.
2. Click Copy on the shortcut menu.
3. While pressing CTRL, select each hyperlink you want to deactivate.
4. Click Paste Special on the Edit menu.
5. Under Operation, click Multiply and then click OK.

 

[Guest]

Joni, that was brilliant. Thank you

There is another way (from excel help!!):
To Deactivate several hyperlinks at once:
1. Type the number 1 in a blank cell, and right-click the cell.
2. Click Copy on the shortcut menu.
3. While pressing CTRL, select each hyperlink you want to deactivate.
4. Click Paste Special on the Edit menu.
5. Under Operation, click Multiply and then click OK.

 

[Guest]

You are wellcome.

Joni, that was brilliant. Thank you