Tired of getting Microsoft Office documents (Word, Excel, Powerpoint) that are chock-full of hyperlinks that are bright blue and easy to accidentally click, making Word goes berserk and open the link?
Tired of getting Microsoft Office documents (Word, Excel, Powerpoint) that are chock-full of hyperlinks that are bright blue and easy to accidentally click, making Word goes berserk and open the link?
You can easily remove all those pesky URLs with a simple macro to remove all the hyperlinks in a document in one full swoop. With macros available to you in one keystroke (Alt F8) this is quite a convenient way of managing your logic ACROSS all Office software — the basic logic remains the same. Let’s see some code now:
A. Microsoft Word
- From a Word document, press ALT F11 to open the MS Visual Basic.
- The Visual Basic interface will open up. From the INSERT menu, click on MODULE to add a module.
- A new document opens up. In there, paste the following code:
Sub RemoveHyperLinksGLobally() Dim i As Integer For i = ActiveDocument.Hyperlinks.Count To 1 Step -1 ActiveDocument.Hyperlinks(i).Delete Next i End Sub
- Press CTRL S to save this module.
- Now, from the FILE menu, select “CLOSE AND RETURN TO MICROSOFT WORD”.
- When back into your Word document, now just press ALT F8 to bring up the MACROS selection window (or you can always do TOOLS –> MACRO –> MACROS)
Not to worry, this does not delete the text, it just converts the hyperlink fields to plain text. However, if your original hyperlink had some formatting and you don’t want to lose that formatting for the text, but just want to get rid of the underlying URL, here’s some handy code for that too:
Sub RemoveHyperLinksGLoballyButPreserveCharacterStyle() Dim i As Long, myRange As Range For i = ActiveDocument.Hyperlinks.Count To 1 Step -1 With ActiveDocument.Hyperlinks(i) Set myRange = .Range .Delete myRange.Style = wdStyleHyperlink End With Next i End Sub
B. Microsoft Excel
- Same process as a MS Word document. From Excel, fire up the VB editor (ALT F11).
- The Visual Basic interface will open up. From the INSERT menu, click on MODULE to add a module.
- A new document opens up. In there, paste the following code:
Sub RemoveHyperLinksGLobally() Dim i As Integer For i = ActiveSheet.Hyperlinks.Count To 1 Step -1 ActiveSheet.Hyperlinks(i).Delete Next i End Sub
- The only difference from the code for MS Word is highlighted in green. ActiveSheet, instead of ActiveDocument.
- Now, from the FILE menu, select “CLOSE AND RETURN TO MICROSOFT EXCEL”.
- When back into your Excel document, now just press ALT F8 to bring up the MACROS selection window (or you can always do TOOLS –> MACRO –> MACROS) and select the right macro. Bingo.
However, since we only work with the “ActiveSheet” in the above example, we remove the hyperlinks only from the single active sheet that is currently on the screen. To remove all the hyperlinks from all the worksheets in an Excel document, this code is handy:
Sub RemoveHyperLinksGLoballyFromAllWorksheets() Dim i As Integer, wSheet As Worksheet For Each wSheet In Worksheets For i = wSheet.Hyperlinks.Count To 1 Step -1 wSheet.Hyperlinks(i).Delete Next i Next wSheet End Sub
Hopefull, this gives you ideas on how to author other macros too. With slight tweaks in code, they can be used across all Office software. Macros are powerful ways of making your editing fast. Plus, they are only a keystroke away (ALT F8) whenever you need them!
Need a primer to Microsoft Office macros? Read this interesting 101 piece from PC World first: http://snipurl.com/macros.
This post is tagged Tips/Tricks
56 Comments
Thank you, thank you, thank you!
Well done. Although some hyperlinks are still left unremoved.
works great! thanks for making it public & free!
Didnt work for me but I found a better way.
On the Edit menu, click Select All or CTRL+A.
Then CTRL+SHIFT+F9 on your keyboard. Duh!
Thanks KP, but that one is easy googleable..the macro will work for more than one occasions (especially to used in other macros, which I find very useful).
Why did the macro not work for you though?
I didn’t have any luck with the macro either but the keyboard shortcut worked perfectly for me too! I’m not real familiar with creating macros so the errors could be mine! At any rate I’m thrilled to not have to do each hyperlink separately!! I couldn’t even do the ‘record macro’ function and use the keyboard shortcuts mentioned to set up the macro…! At any rate, thanks to all ’cause I’m at least closer than I was!!
KP That was great!!!!!!!!!!!!!!!!!!…
The macro didnt work for me too
Hi Rashmi,
The method described above works for ALL MS Word installations I have tried it on — and this includes Office 97 through XP, En/Cn/Jp/Es/Kr versions. Let me know what error(s) you get, should be a cinch to get it sorted out.
Just so you know:
(1) If you are such a freak for keyboard shortcuts, macros are just one keystroke away — ALT F8. Most of my functions are in that one keystroke, instead of remembering CTRL SHIFT F9 for this, CTRL SHIFT SOMETHING ELSE for that etc etc.
(2) Macros are faster than “Select All” (Ctrl A) functionality. If you know the first thing about how Office works, you’ll know that the latter takes a lot of RAM, especially on large documents. On a 2-3 page Word document, it doesn’t matter what you use.
(3) Thirdly, and more importantly, as you will see when I modify/add to this article above, the macro is very useful for tech-savvy folk because the same logic can be used across all Office tools. Try CTRL+SHIFT+F9 in Excel and we’ll talk.
Cheers,
Shanx
Shashank, this is very cool but can you post one that removes all hyperlinks globally from all worksheets (in Excel) AND leaves other text formatting alone? You did that for the Word macro and I tried to modify your Excel macro but was unsuccessful. Thanks!!
Good Work Shanx. It worked great for me.
Thanks
Sridhar
You can do this in Excel: With the excel ssheet open, press ALT 11. select Module under the Inser menu.
Type this into the new module:
Sub RemoveHyperlinks()
‘Remove all hyperlinks from the active sheet
ActiveSheet.Hyperlinks.Delete
End Sub
Then,select “Close and REturn to Microsoft Excel”
then run the macro called “RemoveHyperlinks” in your excel ssheet…
Hi Colleen, this is the code I had before editing this entry. But that version did not always work for everyone, a bug in MS Office I guess. It’s useful to know anyway, thanks.
Hate to be a Johnny Come Lately, but I just found and used this. Very nice, and thanks!
It work perfect! Thanx!
Terrific macro — it did just what I wanted. Thanks!
did not work!
BUT THANKS TO REPLY NO.4 I GOT RID OF THOSE PESKY BASTARDS!
Thank you K.P.!
“a.h.”, can you tell me what version of Word you’re using?
Hi–
Works great in Word, but is there any way to do the same thing in PowerPoint (i.e. remove all hyperlinks from all slides at once)? The Word macro doesn’t do anything in PP.
Thanks!
kate austin
P.S. Re PowerPoint– I tried substituting “Presentation” for “Document” in the macro, but still no go.
Thanks again,
kate
HOW DO I STOP IE ACCESS FROM WORD AND EXCEL?
thanks!
Useful code. Thanks.
Excellent macro for word document: I followed the following:
Opened the document
Pressed alt F8
Pressed create button
pasted the following:
Sub RemoveHyperLinksGLoballyButPreserveCharacterStyle()
Dim i As Long, myRange As Range
For i = ActiveDocument.Hyperlinks.Count To 1 Step -1
With ActiveDocument.Hyperlinks(i)
Set myRange = .Range
.Delete
myRange.Style = wdStyleHyperlink
End With
Next i
End Sub
saved
returned to main document
tool … macros
run macro created
viola! all hyperlinks removed!
Hi Guys,
I wanna to add the hyperlinks in my MS Word document automatically. I tried to make it on from autocorrect option. I also uninstalled the MSoffice and reinstalled. But still the problem is there. So can anybody suggest me solution for how to insert hyperlinks automatically.
Thanks
Santosh
bad ass this macro is- turned my pain into cynical pleasure- go to hell hyperlinks!
The macro worked perfectly for me.
A very large THANK YOU!
Thanks, Works in Mac for Office (v 2004). Slight alterations - when returning to the word doc from the macro you’ll find the quit from macro under the “Word” menu, not file. Worked great, saved me tons of time.
Your code is perfct but it does not remove all hyperlinks from a worksheet.
Suppose worksheet has a organization chart with a hyperlink then it dosn’t removes it…
I have tried following thing but not removing from organization chart….
Dim objexcel As Excel.Application
Dim objworksheet As Excel.Worksheet
Dim objworkbook As Excel.Workbooks
Dim hlink As Excel.Hyperlinks
Private Sub cmddelete_Click()
Set objexcel = CreateObject(”Excel.Application”)
objexcel.Workbooks.Open (”c:\test hyper.xls”)
objexcel.WindowState = xlMinimized
objexcel.WindowState = xlMaximized
Dim Shp As Excel.ShapeRange
Dim IShp As Excel.Shape
Dim i As Integer
Dim j As Integer
On Error GoTo ResNextShp
For j = 1 To objexcel.ActiveSheet.Shapes.Count
objexcel.ActiveSheet.Shapes(j).Select
Set Shp = Selection.ShapeRange
If Shp.HasDiagramNode = msoTrue Then
For i = 1 To Shp.DiagramNode.Diagram.Nodes.Count
Set IShp = Shp.DiagramNode.Diagram.Nodes(i).Shape
If IShp.Hyperlink.Address > “” Then
IShp.Hyperlink.Delete
End If
Next i
End If
Set IShp = objexcel.ActiveSheet.Shapes(j)
If IShp.Hyperlink.Address > “” Then
IShp.Hyperlink.Delete
End If
Next j
i = 0
For i = objexcel.ActiveSheet.Hyperlinks.Count To 1 Step -1
objexcel.ActiveSheet.Hyperlinks(i).Delete
Next i
Exit Sub
ResNextShp:
Resume Next
End Sub
Please someone enlighten me. I have a document where I actually want to delete any text which is a hyperlink. ie. take all the links out of document
Thanks in advance
Great tool - but in my documents I have a table of contents (from Insert>Reference>Index and Tables) which hyperlinks within the document, and for some reason, when I run the macro, the hyperlinks in the table of contents are removed, but the text is formatted as hyperlinked (blue and underlined) when they weren’t before the macro was run. I’d like the table of contents to remain as formatted before - any way to to this? Thanks.
I need the opposite of this. I have hundreds of Pictures (picture 1, picture 2 etc.) with hyperlinks associated with them. I want a macro to run through all of the pictures and then write the associated hyperlink in text format in a row (a1, a2, a3 etc)
Ideas please?
It work perfect…………….
Thank you so much……………………………….
thanks mate, swell job and a real help.
The macro was very useful…but there has been a slight glitch.
By mistake I have applied the Word document Macro to all templates and documents. As a result whenever I copy and paste any web page into microsoft word, all the hyperlinks get converted into plaintext. Tell me a way to remedy this problem.
Thanks a lot.
I have tried this on a large spreadsheet and keep getting a run time error.
Run time error ‘6′:
Overflow
When I open the debugger it points to the following line:
For i = ActiveSheet.Hyperlinks.Count To 1 Step -1
I am not a program, so I don’t know what is going on. I am using Excel 2000.
Any help would be appreciated.
Thanks
Nevermind, figured it out.
In the second line, I changed integer to long. Interger go up to 32767.
It works fine now. Looks like this.
Sub RemoveHyperLinksGLobally()
Dim i As Long
For i = ActiveSheet.Hyperlinks.Count To 1 Step -1
ActiveSheet.Hyperlinks(i).Delete
Next i
End Sub
I have been trying to write a macro that will create an hyperlink from one tab to another tab within my excel workbook.
This macro needs to be flexible so I’m trying to embed a variable like “strtabname & !A1″ to set the hyperlink’s link to cell A1 of a particular tab.
So far I can create an inactive hyperlink. Suggestions will be most appreciated, this is driving me crazy and I have yet to find a suitable example online or amongst co-workers. Thank you.
Thanks! Worked great - saved me hours!!
Can somebody please tell me how to convert text that looks like a URL programmatically to a hyperlink. I know this feature can be incorporated by autoformating but I want to do it programmatically.
THANK YOU!!! THANK YOU!!! THANK YOU!!! THANK YOU!!! THANK YOU!!! THANK YOU!!! THANK YOU!!! THANK YOU!!!
Your little bit of code has saved me G*d only knows how many hours of frustration!
I only wish I’d found your tip sooner, as I’ve been removing links from Word docs (I use Word 97) manually for years.
Again,
THANK YOU SO MUCH!
I had a similar problem, I pasted a page with a bunch of “email this contact” links from a web page into Word. Then I wanted to convert all the hyperlinks Word made into just plain text email addresses rather than a blue link that said “email this contact”.
This script did the trick:
Sub ChangeEmailAddressHyperlinkToPlainText()
‘
‘ Macro
‘ Macro created 8/19/2006 by Brent Hugh
‘
Dim i As Integer
For i = ActiveDocument.Hyperlinks.Count To 1 Step -1
ActiveDocument.Hyperlinks(i).Range = ActiveDocument.Hyperlinks(i).Address
Next i
End Sub
Hey,
this is so cool. i work with gigantic word docs full with links that i m supposed to remove. this is a blessing for me.
thanks a zillion.
Thanks!!! I was having a really hard time trying to delete the hyperlinks from my 30+ page document and found this. After I did the Macro thing, all the hyperlinks disappeared like magic. Thank a million!!!!!!! You can’t believe how grateful I am right now. =)
Ecellent! Thanks!
Solution for Dan [29]:
This means finding text with different format (usualy Blue + Underlined), and replacing with “nothing” for each found item. Here is a simple method:
Open Find/Replace dialog box
Find What: Font: Underline, Font color: Blue
will find all Underline and Blue strings, and
Replace With: (leave box empty)
click ‘Replace All’
This is how you would do it in PowerPoint (works in 2003, not sure of earlier versions):
Sub NoHyperlinks()
Dim i As Long
Dim hl As Hyperlink
For i = 1 To ActivePresentation.Slides.Count
For Each hl In ActivePresentation.Slides(i).Hyperlinks
hl.Delete
Next hl
Next i
End Sub
I have tried all of the above to get rid of hyperlinks from a formatted inventory (mostly color fill, font colors &sizes, EXCEL worksheet I use everyday. I cannot get rid of them. For awhile (about a week) it worked, but today when I opened up the sheet (with macros enabled) it was riddled with them….worse than before. If I run:
Sub RemoveHyperLinksGLoballyFromAllWorksheets()
Dim i As Integer, wSheet As Worksheet
For Each wSheet In Worksheets
For i = wSheet.Hyperlinks.Count To 1 Step -1
wSheet.Hyperlinks(i).Delete
Next i
Next wSheet
End Sub
I get a run time error and Excel auto recovers and closes. I have run others (in addition to the above) that I have found on Google. But to no avail. Where do these d*** things come from. I’ve never added a hyperlink anywhere in this worksheet, but now every cell across and down for 171 rows has the same one. It’s an inventory document and even if I CAN get rid of them, I will have to reformat 2200+ cells to reflect inventory changes. HELP!!!
thank you this actually worked
but the problem is that is does get rid of the hyperlinks but not the hyperlinks of any pictures
Thank you very much! This has been a real help to me.
To rempve all hyperlinks in word. Ctrl-A then Ctrl-Shift-F9
Done
Thank you. Great!
ping back in Lithuanian from http://www.karalius.wordpress.com
Thanks for this.
I have been struggling with this problem for a couple years because it slows down my computer when I load and/or scroll through documents.
For all of y’all with macs, it works on it too…
Thank you so much. I’ve been trying to figure this out for *ages*!
*Very* much appreciated.
OMG! I actually did a happy dance. Thank you so much. This was extraordinarily helpful.
both worked fine for me
Thanks both of you
Brilliant, I’ve never used macro before but the instructions were v. easy to follow and it took a second to work. Thought I’d spend the time I will now be saving to write a thank you!
Incoming Links