Results 1 to 3 of 3
  1. #1
    3 Star Lounger baumgrenze's Avatar
    Join Date
    Feb 2001
    Location
    California, USA
    Posts
    262
    Thanks
    6
    Thanked 0 Times in 0 Posts

    Macro to Remove Hyperlink (2000 9.0.3821 SR-1)

    I used IE 5.50.4522.1800IC to copy and paste account activity information from a mutual fund into a spread sheet. All of the items in the 'description' column on the original web page are hyperlinked to detailed information. Even with that web page open in IE, the links saved in the spread sheet do not run. The links do make the Excel file much larger.

    I tried to record a macro to unlink them (move down one cell, right click, choose hyperlink, choose remove hyperlink). When I checked the macro it specified the address of the exact cell into which I had moved the cursor rather than recording the action (move down one cell.)

    Is there a simple way to get the action recorded?

    Do I need to be concerned that the macro recognize when there are no further hyperlinks to find and remove, i.e., would a crude macro just keep moving down one cell and trying forever?

    Perhaps something as simple as an iterative process that worked for 10 or 20 cells in succession would be a kluge to get around this problem?

    Thanks for any insights,

    John
    Baumgrenze
    Hier sind wir tief eingewurzelt.

  2. #2
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Missouri, USA
    Posts
    103
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro to Remove Hyperlink (2000 9.0.3821 SR-1)

    Not sure if this is what you what, but Microsoft has a "Delete Links" Add-in that might already solve your problems. Check MS web site.

  3. #3
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Macro to Remove Hyperlink (2000 9.0.3821 SR-1)

    I'm a little unclear about whether you want to delete hyperlinks or modify them. This code run against a selected range of the worksheet will disable them as hyperlinks while maintaining the link text.

    Sub DisableHyperLinks()
    Selection.Hyperlinks.Delete
    End Sub

    To completely delete and clear conventional hyperlinks to websites within a selected range, try the following:

    Sub KillHyperLinks()
    Dim rngActon As Range, rngCell As Range
    Application.ScreenUpdating = False
    Set rngActon = Selection.SpecialCells(xlCellTypeConstants, xlTextValues)
    If Not rngActon Is Nothing Then
    For Each rngCell In rngActon
    If InStr(rngCell.Value, "http://") > 0 Then rngCell.ClearContents
    Next rngCell
    End If
    Set rngActon = Nothing
    Application.ScreenUpdating = False
    End Sub

    HTH, if not post back. (There may be other better ways, I didn't spend a long time looking in VBA Help.)
    -John ... I float in liquid gardens
    UTC -7ąDS

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •