Excel 2000 hyperlink problem

[Ken]

I have a spreadsheet of personnel from other offices that
has the usual items: Name, Address, City..., Phone,
emails, etc.

For some reason in the middle of the worksheet, every cell
has an email hyperlink, and not necessarily for the
associated person. It's in cells that are not even email
addresses. If I try to remove the hyperlink, Excel hangs-
up and I have to task-manager out of it.

Some of the items have been copied and pasted from another
workbook, but there were no macros involved, if that means
anything to you.

So my question is, how do I either remove the hyperlinks
from specific cells or from the entire workbook?
Thanks.

 

[Olly]

range(myrange).hyperlinks.delete

 

[Guest]

Sorry Olly, I don't follow you. Is this supposed to be a
formula?

 

[Olly]

No it's a bit of VBA code, to programmatically remove hyperlinks for a given
range.

If you want to do it manually, rather than programmatically, simply
right-click the cells containing hyperlinks and choose 'Remove Hyperlink'

 

[Ken]

Thanks, but as I said, if I try to remove the hyperlink
(via right-clicking), Excel hangs-up and I have to task-
manager out of it.
I don't know how to do VBA, unless someone would like to
do a quick walk-through for me.
Thanks again.

 

[Dave Peterson]

Save your workbook first--just in case.

Select the range of cells to clean up.
hit alt-f11 (to get to the VBE)
hit ctrl-G (to see the immediate window)

Type this in and hit enter:
selection.hyperlinks.delete

(if you wanted the whole sheet, you can select all the cells via ctrl-A.)

=====
If you want to have this kind of thing readily available to all of your
workbooks, you may want to read more about macros:

David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
is a good place to start.

 

[Ken]

Thanks again for the help, but the workbook locked up
again even doing the VBE. I've let it run for about 5
minutes now (task mgr is at 100% usage and is showing
Excel unresponsive.) So I guess I'll have to either redo
the workbook or live with it.
FYI - the file size, long before doing the VBE, changed
from 51K to 1100K at some point, so that will likely be my
start over point.

 

[Dave Peterson]

One more attempt:

copy an empty cell
select your range (column(s)) with hyperlinks
edit|paste special|click add

You may have to reformat those cells, though.

=====
Just curious, do you get that same unresponsiveness if you limit your range to
something smaller than the whole worksheet?

 

[Ken]

Thanks again Dave.
No luck on your alternate method. I've tried the entire
worksheet as well as tried limiting the cells. My IS
department is stumped as well. I appreciate your time
spent on this.
Ken

 

[Dave Peterson]

Do any of those techniques work on a test workbook?

Maybe there's something wrong with the worksheet/workbook?

I know I've read posts where people have said they stay away from using
hyperlinks (Insert|Hyperlink type) and use the worksheet function (=hyperlink())
instead.

But that was awhile ago--maybe xl97 era. But I still follow that
recommendation--if I have lots of links, I'll try to use =hyperlink() as my
first choice.