how do I remove many hyperlinks from a spreadsheet at one time?

[Guest]

 

[Dave Peterson]

Select all the cells (ctrl-A (twice in xl2003))
hit alt-f11 (to get to the VBE, where macros live)
hit ctrl-g (to see the immediate window)

type this and hit enter:

selection.hyperlinks.delete

Close the VBE and back to excel to see if it worked.

===
You could even just select a smaller range and just clean that since the command
uses the current selection.

davey wrote:

 

[Don Guillett]

It is considered bad manners to ask your question in the subject line.
Perhaps
Delete all hyperlinks
then ask the question in the BODY of the message

A macro will do this for you
activesheet.hyperlinks.delete

 

[Guest]

Thanks for the help, however you could have left YOUR comments out of the
body of the message.

 

[Dave Peterson]

By having the question in the subject line, you're taking a chance that the
question may get truncated.

And lots of people will ignore your post if you don't put your question in the
body of the message.

Don was trying to help.

 

[Guest]

Hi Dave!
I just wanted to thank you for this posting. I have been frustrated by this
issue for about a week now and finally stumbled across your posting. What a
relief. It solved my problem and I am eternally grateful to you

Thanks again – may all that good cycle back your way when you least expect it!

Heather

 

[Dave Peterson]

Glad it worked ok for you.

Hi Dave!
I just wanted to thank you for this posting. I have been frustrated by this
issue for about a week now and finally stumbled across your posting. What a
relief. It solved my problem and I am eternally grateful to you

Thanks again – may all that good cycle back your way when you least expect it!

Heather

 

[Guest]

Hi Dave - Heather again...Is there a simple way to do the reverse of this
command - as in selection.hyperlinks.add in order to create email links from
text? I've seen a lot of more complicated VB coding but it's beyond me and
my simple worksheet.

You have a way of makng the complicated simple

 

[Dave Peterson]

If you have the email addresses in a column, you can use a helper column that
contains a formula like:

=hyperlink("mailto://" & a1)

And drag down.

If you want to use the VBA code you've found, post back and include that code.
(Personally, I think I like the =hyperlink() worksheet function more than the
insert|hyperlink stuff.

 

[Guest]

Thx! I'll give it a go...

Have a great day!

If you have the email addresses in a column, you can use a helper column that
contains a formula like:

=hyperlink("mailto://" & a1)

And drag down.

If you want to use the VBA code you've found, post back and include that code.
(Personally, I think I like the =hyperlink() worksheet function more than the
insert|hyperlink stuff.

 

[Guest]

Bless you for this one! Annie

 

[Beverly Zellner]

Select all the cells (ctrl-A (twice in xl2003))
hit alt-f11 (to get to the VBE, where macros live)
hit ctrl-g (to see the immediate window)

type this and hit enter:

selection.hyperlinks.delete

Close the VBE and back to excel to see if it worked.

===
You could even just select a smaller range and just clean that since the command
uses the current selection.

davey wrote:

 

[Beverly Zellner]

Dave Peterson is a very smart person!!!!!

I love you Dave!!!!

Thank you for taking the time to write this because you saved me from
manually going in to 1,843 cells and removing hyperlinks.

 

[Dave Peterson]

Glad it helped!

Dave Peterson is a very smart person!!!!!

I love you Dave!!!!

Thank you for taking the time to write this because you saved me from
manually going in to 1,843 cells and removing hyperlinks.

 

[judigentry]

Select all the cells (ctrl-A (twice in xl2003))
hit alt-f11 (to get to the VBE, where macros live)
hit ctrl-g (to see the immediate window)

type this and hit enter:

selection.hyperlinks.delete

Close the VBE and back to excel to see if it worked.

===
You could even just select a smaller range and just clean that since the command
uses the current selection.

davey wrote:

 

[judigentry]

Dear Dave,
Thank you so much for your response. You saved me hours of time! I had 903
lines, and I wasted so many hours until I found your post....thank you, thank
you, thank you for taking your time to help people with this problem! It
worked beautifully. I also have another question. Along with my hyperlinks, I
have a graphic in the upper left hand corner of each. If I click on those the
hyperlink is still active. Can you tell me how to delete those too (without
having to delete them one by one)? Thank you again.

 

[Dave Peterson]

You could use a macro:

Option Explicit
Sub testme()

Dim myShape As Shape

On Error Resume Next
For Each myShape In ActiveSheet.Shapes
myShape.Hyperlink.Delete
Next myShape
On Error GoTo 0

End Sub

 

[M]

Dave - Your VBE to select a section of hyperlinks and delete them works
great! I would like to include this as a VBA sub (macro) and put it into
my tool bar or ribbon or other easly accessed button in Excel 2007 but I am
VBA/E/Macro challenged but can follow explecit directions and have other
add-ins and macros that I have managed to "add-in" could you help me with
creating the VBA sub (macro) to do this?
Thank you,
M

 

[Dave Peterson]

You'd want a macro (or two) that does:

Option Explicit
Sub DeleteHyperLinksFromSelection()
Selection.hyperlinks.delete
End Sub
Sub DeleteAllHyperLinks()
activesheet.cells.hyperlinks.delete
End sub

Then you could use alt-f8 to invoke either macro.

If you want an item on the addins tab (in xl2007):

For additions to the worksheet menu bar, I really like the way John Walkenbach
does it in his menumaker workbook:
http://j-walk.com/ss/excel/tips/tip53.htm

Here's how I do it when I want a toolbar:
http://www.contextures.com/xlToolbar02.html
(from Debra Dalgleish's site)

And if you use xl2007:

If you want to learn about modifying the ribbon, you can start at Ron de Bruin's
site:
http://www.rondebruin.nl/ribbon.htm
http://www.rondebruin.nl/qat.htm -- For macros for all workbooks (saved as an
addin)
or
http://www.rondebruin.nl/2007addin.htm

In xl2007, those toolbars and menu modifications will show up under the addins.

 

[M]

Dave...... Thank you - Dave -- Dave Peterson not only answered my
immediate question but supplied additional helpful content, references, and
directions to allow me to follow up and act on the recommendations - Very
Helpful
Thank you
M