Hi,

I am trying to remove all of the hyperlinks in my (giant 100 meg) work
book or any subset of the workbook, but everything I have tried has
failed.

I can right click on each cell and remove the link, but that will take
weeks.

I tried the following:

1) Multiply a selection by 1. This does not work. I can multiply a
single cell by one, but if I do a selection, it does not work.

2)*This crashes my workbook*
Sub DeleteHyper()
ActiveSheet.Hyperlinks.Delete
End Sub



3) *Again, this does not work if I select more than on cell at a time*

Sub DeleteHyper()
selection.Hyperlinks.Delete
End Sub



4) *This crashes excel. I got this off of MS's site and it was
supposed to select all the cells with a hyperlink*

'This Sub procedure selects all cells in the worksheet that contain
'hyperlinks. You can then clear the selected cells to delete all of
'the hyperlinks.
Sub SelectAllHyperlinkCells()
FirstCell = 1
For Each xLink In ActiveSheet.Hyperlinks
If FirstCell = 1 Then
Set xRange = xLink.Range
FirstCell = 0
Else
Set xRange = Application.Union(xRange, xLink.Range)
End If
Next xLink
xRange.Select
End Sub




I am wondering if my copy of excel has been corrupted somehow since all
of these things that "should" work just don't. I tried working with a
single tab and moving it out of the workbook, but that had no effect.

Thanks for any advice!


--
Tai-Pan
------------------------------------------------------------------------
Tai-Pan's Profile: http://www.excelforum.com/member.php...o&userid=28269
View this thread: http://www.excelforum.com/showthread...hreadid=478339

i may be totally off the mark, but maybe the reason you're crashing is
because your file is HUGE!!! What is in this file that makes it so
large? Is there any way you can reduce the size?


--
tkaplan
------------------------------------------------------------------------
tkaplan's Profile: http://www.excelforum.com/member.php...o&userid=22987
View this thread: http://www.excelforum.com/showthread...hreadid=478339

That is a good point and I thought the same thing.
However, I selected a single (small) tab out of the main file and
tested the above formulas on it as well with no luck.

As far as why the main file is huge, 15 tabs, 30 Vlookup columns, and
several hundred thousand cells with formulas.


--
Tai-Pan
------------------------------------------------------------------------
Tai-Pan's Profile: http://www.excelforum.com/member.php...o&userid=28269
View this thread: http://www.excelforum.com/showthread...hreadid=478339

Have you tried doing it on another machine?
************
Anne Troy
www.OfficeArticles.com

"Tai-Pan" wrote in
message ...

Hi,

I am trying to remove all of the hyperlinks in my (giant 100 meg) work
book or any subset of the workbook, but everything I have tried has
failed.

I can right click on each cell and remove the link, but that will take
weeks.

I tried the following:

1) Multiply a selection by 1. This does not work. I can multiply a
single cell by one, but if I do a selection, it does not work.

2)*This crashes my workbook*
Sub DeleteHyper()
ActiveSheet.Hyperlinks.Delete
End Sub



3) *Again, this does not work if I select more than on cell at a time*

Sub DeleteHyper()
selection.Hyperlinks.Delete
End Sub



4) *This crashes excel. I got this off of MS's site and it was
supposed to select all the cells with a hyperlink*

'This Sub procedure selects all cells in the worksheet that contain
'hyperlinks. You can then clear the selected cells to delete all of
'the hyperlinks.
Sub SelectAllHyperlinkCells()
FirstCell = 1
For Each xLink In ActiveSheet.Hyperlinks
If FirstCell = 1 Then
Set xRange = xLink.Range
FirstCell = 0
Else
Set xRange = Application.Union(xRange, xLink.Range)
End If
Next xLink
xRange.Select
End Sub




I am wondering if my copy of excel has been corrupted somehow since all
of these things that "should" work just don't. I tried working with a
single tab and moving it out of the workbook, but that had no effect.

Thanks for any advice!


--
Tai-Pan
------------------------------------------------------------------------
Tai-Pan's Profile:
http://www.excelforum.com/member.php...o&userid=28269
View this thread: http://www.excelforum.com/showthread...hreadid=478339

Hi,

Yes we tried it on a different machine.


--
Tai-Pan
------------------------------------------------------------------------
Tai-Pan's Profile: http://www.excelforum.com/member.php...o&userid=28269
View this thread: http://www.excelforum.com/showthread...hreadid=478339

I'm not sure if this will work, either...

Sub DeleteHyper2()
dim myHyper as hyperlink
for each myhyper in activesheet.hyperlinks
myhyper.delete
next myhyper
End Sub

or...

Sub DeleteHyper3()
dim iCtr as long
for ictr = 1 to activesheet.hyperlinks.count
activesheet.hyperlinks(1).delete
next ictr
End Sub

I've seen things like:
activesheet.pictures.delete
fail to work--but it never crashed excel (But I've never seen a 100meg workbook,
either).

Tai-Pan wrote:

Hi,

I am trying to remove all of the hyperlinks in my (giant 100 meg) work
book or any subset of the workbook, but everything I have tried has
failed.

I can right click on each cell and remove the link, but that will take
weeks.

I tried the following:

1) Multiply a selection by 1. This does not work. I can multiply a
single cell by one, but if I do a selection, it does not work.

2)*This crashes my workbook*
Sub DeleteHyper()
ActiveSheet.Hyperlinks.Delete
End Sub

3) *Again, this does not work if I select more than on cell at a time*

Sub DeleteHyper()
selection.Hyperlinks.Delete
End Sub

4) *This crashes excel. I got this off of MS's site and it was
supposed to select all the cells with a hyperlink*

'This Sub procedure selects all cells in the worksheet that contain
'hyperlinks. You can then clear the selected cells to delete all of
'the hyperlinks.
Sub SelectAllHyperlinkCells()
FirstCell = 1
For Each xLink In ActiveSheet.Hyperlinks
If FirstCell = 1 Then
Set xRange = xLink.Range
FirstCell = 0
Else
Set xRange = Application.Union(xRange, xLink.Range)
End If
Next xLink
xRange.Select
End Sub

I am wondering if my copy of excel has been corrupted somehow since all
of these things that "should" work just don't. I tried working with a
single tab and moving it out of the workbook, but that had no effect.

Thanks for any advice!

--
Tai-Pan
------------------------------------------------------------------------
Tai-Pan's Profile: http://www.excelforum.com/member.php...o&userid=28269
View this thread: http://www.excelforum.com/showthread...hreadid=478339

--

Dave Peterson