Remove Hyperlink

[chits]

I have several cells in my worksheet which is having a Hyperlink..
I want to remove this hyperlink for all the cells in that row..
is this possible through a single command?
currently i have to select each & every cell -> right click & remore link
individually?
i have the text in different fonts for this row, Is it possible to remove
hyperlinks for all at a strech retaining the font?
Thanks,
Chitra

 

[chits]

Please note: i am having Macros disabled in this sheet by the system & i cant
change the settings to enable it.

 

[Dave Peterson]

You can remove the hyperlinks from a selected area by running a small macro:

option Explicit
sub removeLinks()
selection.hyperlinks.delete
end sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[chits]

I have a wide range of coverage here
around 900 rows...
seems.. This command does not work for this selection.

if i try running this macro for 2-3 rows, it works but even removes the
formatting in these selected rows ...
can you suggest me some other solutions to this that could retain the
formatting...?

Also i had not created these links on this sheet.. it came sporadically...
can be seen even on blank cells...

Any other solutions? (i m using MS Excel 2003 (11.5612.5606) version)

 

[Dave Peterson]

I've only seen the formatting get removed from the cells that had the
hyperlink. Other cells remained unchanged. I don't have a way to retain all
the formatting for those cells.

And I've never seen it fail no matter how large the selection is. In fact, I've
removed all the hyperlinks from the whole sheet with no problems.

 

[David McRitchie]

When you select all cells all cells on the sheet should get the selection
shading. In Excel 2003 Ctrl+A does not select all cell, you must
use Ctrl+A twice to select all cells; otherwise, it is the equivalent
of Ctrl+* (for the current region). That little item alone would keep
me from upgrading to Excel 2003.

I have a macro solution to that see
Shortcut Keys in Excel 2000 (Excel Version 9)
"Excel 2003 additions (#foobar)"
http://www.mvps.org/dmcritchie/excel/shortx2k.htm#foobar

Was the failure to select all cell your problems.

Another way would be to change the macro that was provided
from
selection.hyperlinks.delete
to
cells.hyperlinks.delete
which I would avoid, if possible, because you then lose flexibility and would
probably want to have two macros instead of one.

Obviously if you are running the macros you are past your problem of not
being able to run macros. Not familiar with the lost formatting problem.
My own Excel is 2000.

One more thing sounds like the data is coming in from HTML, so you may
have some additional problems not yet noticed.
To get rid of non-breakikng space characters (seem to be spaces but aren't)
see http://www.mvps.org/dmcritchie/excel/join.htm#trimall.htm

To get rid of shapes, buttons etc.
see http://www.mvps.org/dmcritchie/excel/shapes.htm

HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm