Home |
Search |
Today's Posts |
#1
|
|||
|
|||
how do I remove many hyperlinks from a spreadsheet at one time?
|
#2
|
|||
|
|||
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: -- Dave Peterson |
#3
|
|||
|
|||
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 -- Don Guillett SalesAid Software "davey" wrote in message ... |
#4
|
|||
|
|||
Thanks for the help, however you could have left YOUR comments out of the
body of the message. "Don Guillett" wrote: 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 -- Don Guillett SalesAid Software "davey" wrote in message ... |
#5
|
|||
|
|||
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. davey wrote: Thanks for the help, however you could have left YOUR comments out of the body of the message. "Don Guillett" wrote: 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 -- Don Guillett SalesAid Software "davey" wrote in message ... -- Dave Peterson |
#6
|
|||
|
|||
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" wrote: 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: -- Dave Peterson |
#7
|
|||
|
|||
Glad it worked ok for you.
hamcdo wrote: 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" wrote: 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: -- Dave Peterson -- Dave Peterson |
#8
|
|||
|
|||
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" wrote: 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: -- Dave Peterson |
#9
|
|||
|
|||
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. hamcdo wrote: 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" wrote: 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: -- Dave Peterson -- Dave Peterson |
#10
|
|||
|
|||
Thx! I'll give it a go...
Have a great day! "Dave Peterson" wrote: 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. hamcdo wrote: 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" wrote: 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: -- Dave Peterson -- Dave Peterson |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
how do I remove many hyperlinks from a spreadsheet at one time
Bless you for this one! Annie
"Dave Peterson" wrote: 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: -- Dave Peterson |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
how do I remove many hyperlinks from a spreadsheet at one time
"Dave Peterson" wrote: 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: -- Dave Peterson |
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
how do I remove many hyperlinks from a spreadsheet at one time
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" wrote: 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: -- Dave Peterson |
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
how do I remove many hyperlinks from a spreadsheet at one time
Glad it helped!
Beverly Zellner wrote: 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" wrote: 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: -- Dave Peterson -- Dave Peterson |
#15
Posted to microsoft.public.excel.misc
|
|||
|
|||
how do I remove many hyperlinks from a spreadsheet at one time
"Dave Peterson" wrote: 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: -- Dave Peterson |
#16
Posted to microsoft.public.excel.misc
|
|||
|
|||
how do I remove many hyperlinks from a spreadsheet at one time
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" wrote: 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: -- Dave Peterson |
#17
Posted to microsoft.public.excel.misc
|
|||
|
|||
how do I remove many hyperlinks from a spreadsheet at one time
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 judigentry wrote: 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" wrote: 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: -- Dave Peterson -- Dave Peterson |
#18
Posted to microsoft.public.excel.misc
|
|||
|
|||
how do I remove many hyperlinks from a spreadsheet at one time
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" wrote: 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: -- Dave Peterson |
#19
Posted to microsoft.public.excel.misc
|
|||
|
|||
how do I remove many hyperlinks from a spreadsheet at one time
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 wrote: 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" wrote: 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: -- Dave Peterson -- Dave Peterson |
#20
Posted to microsoft.public.excel.misc
|
|||
|
|||
how do I remove many hyperlinks from a spreadsheet at one time
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 "Dave Peterson" wrote: 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 wrote: 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" wrote: 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: -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I remove hyperlinks | Excel Discussion (Misc queries) | |||
How to remove all hyperlinks from a worksheet in Excel? | Excel Worksheet Functions | |||
remove hyperlinks | Excel Discussion (Misc queries) | |||
remove all Hyperlinks at one time. | Excel Discussion (Misc queries) | |||
HOW DO I REMOVE DUPLICATED NAMES ON SPREADSHEET EXCEL97 3900 ROWS. | Excel Worksheet Functions |