• Macro to Remove Hyperlink (2000 9.0.3821 SR-1)

    Home » Forums » AskWoody support » Productivity software by function » MS Excel and spreadsheet help » Macro to Remove Hyperlink (2000 9.0.3821 SR-1)

    Author
    Topic
    #382141

    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

    Viewing 1 reply thread
    Author
    Replies
    • #646819

      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.

    • #646820

      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.)

    Viewing 1 reply thread
    Reply To: Macro to Remove Hyperlink (2000 9.0.3821 SR-1)

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information: