Tags: column, copied, cut, excel, hyperlinks, link, microsoft, msdn, page, paste, removing, software, spreadsheet, table, unfortunately, web

Removing hyperlinks from a whole column

On Microsoft » Microsoft Excel

4,824 words with 5 Comments; publish: Fri, 23 May 2008 20:05:00 GMT; (306171.88, « »)

I have recently cut and paste a table from a web page into a

spreadsheet. Unfortunately it has also copied in the link information

as well. This drives me nuts, as whenever I click on the cell my

browser opens. I have no need for the link so want to get rid of it.

I can remove the link on an individual cell by right-clicking and

selecting Hyperlink->Remove Hyperlink. However I cannot find a way to

do this for the entire column at once.

Thanks for any suggestions.

Craig

All Comments

Leave a comment...

  • 5 Comments
    • try this

      Sub HyperlinksOut()

      ActiveSheet.columns(activecolumn).Hyperlinks.Delete

      End Sub

      "Craig Thomson" <craig.excel.todaysummary.com.spam.free> wrote in message

      news:jc0upv0lv3eboirtno8a0i5cag80ud4tie.excel.todaysummary.com.4ax.com...

      > I have recently cut and paste a table from a web page into a

      > spreadsheet. Unfortunately it has also copied in the link information

      > as well. This drives me nuts, as whenever I click on the cell my

      > browser opens. I have no need for the link so want to get rid of it.

      > I can remove the link on an individual cell by right-clicking and

      > selecting Hyperlink->Remove Hyperlink. However I cannot find a way to

      > do this for the entire column at once.

      > Thanks for any suggestions.

      > Craig

      >

      #1; Fri, 23 May 2008 20:06:00 GMT
    • Craig,

      This code could to it, select the range where the links

      are and run it.

      Sub Remove_hyperlink()

      Dim i As Integer

      Dim myRange As Range

      Set myRange = Selection

      With myRange

      For i = 1 To .Rows.Count

      Selection.Hyperlinks.Delete

      ActiveCell.Offset(1, 0).Range("A1").Select

      Next i

      End With

      End Sub

      HTH,

      Felipe

      >--Original Message--

      >I have recently cut and paste a table from a web page

      into a

      >spreadsheet. Unfortunately it has also copied in the link

      information

      >as well. This drives me nuts, as whenever I click on the

      cell my

      >browser opens. I have no need for the link so want to get

      rid of it.

      >I can remove the link on an individual cell by right-

      clicking and

      >selecting Hyperlink->Remove Hyperlink. However I cannot

      find a way to

      >do this for the entire column at once.

      >Thanks for any suggestions.

      >Craig

      >.

      >

      #2; Fri, 23 May 2008 20:07:00 GMT
    • Craig,

      I can't figure out how to do it in Excel either, but you can easily do =it in the VBA Editor:

      - select the column with the hyperlinks

      - open the VBA Editor - Alt+F11

      - find or open the Immediate Window and enter

      Selection.Hyperlinks.Delete

      and press Return

      If you need to do this on a regular basis, you may need a macro.

      HTH

      Anders Silv=E9n

      "Craig Thomson" <craig.excel.todaysummary.com.spam.free> skrev i meddelandet =news:jc0upv0lv3eboirtno8a0i5cag80ud4tie.excel.todaysummary.com.4ax.com...

      > I have recently cut and paste a table from a web page into a

      > spreadsheet. Unfortunately it has also copied in the link information

      > as well. This drives me nuts, as whenever I click on the cell my

      > browser opens. I have no need for the link so want to get rid of it.

      > > I can remove the link on an individual cell by right-clicking and

      > selecting Hyperlink->Remove Hyperlink. However I cannot find a way to

      > do this for the entire column at once.

      > > Thanks for any suggestions.

      > > Craig

      >

      #3; Fri, 23 May 2008 20:08:00 GMT
    • Correction

      > ActiveSheet.columns(activecolumn).Hyperlinks.Delete

      ActiveSheet.columns(activecell.column).Hyperlinks.Delete

      "Don Guillett" <donaldb.excel.todaysummary.com.281.com> wrote in message

      news:%23jSFmDbnDHA.2416.excel.todaysummary.com.TK2MSFTNGP10.phx.gbl...

      > try this

      > Sub HyperlinksOut()

      > ActiveSheet.columns(activecolumn).Hyperlinks.Delete

      >

      > End Sub

      > "Craig Thomson" <craig.excel.todaysummary.com.spam.free> wrote in message

      > news:jc0upv0lv3eboirtno8a0i5cag80ud4tie.excel.todaysummary.com.4ax.com...

      > > I have recently cut and paste a table from a web page into a

      > > spreadsheet. Unfortunately it has also copied in the link information

      > > as well. This drives me nuts, as whenever I click on the cell my

      > > browser opens. I have no need for the link so want to get rid of it.

      > >

      > > I can remove the link on an individual cell by right-clicking and

      > > selecting Hyperlink->Remove Hyperlink. However I cannot find a way to

      > > do this for the entire column at once.

      > >

      > > Thanks for any suggestions.

      > >

      > > Craig

      > >

      >

      #4; Fri, 23 May 2008 20:09:00 GMT
    • Thanks Don. It was short and sweet and worked a treat!

      Craig

      On Wed, 29 Oct 2003 07:45:20 -0600, "Don Guillett" <donaldb.excel.todaysummary.com.281.com>

      wrote:

      >Correction

      >> ActiveSheet.columns(activecolumn).Hyperlinks.Delete

      > ActiveSheet.columns(activecell.column).Hyperlinks.Delete

      #5; Fri, 23 May 2008 20:10:00 GMT