PDA

View Full Version : links



rodney_malod
05-04-2006, 02:35 AM
i have a spreadsheet with links, i wish to remove these links but retain the infomation.

how can this be done? :doh:

johnske
05-04-2006, 03:38 AM
If you also want to lose the worksheet formulas...

Option Explicit
'
Sub DeleteLinksAndFormulas()
With Cells
.Copy
.PasteSpecial Paste:=xlPasteValues
.Hyperlinks.Delete
End With
End Sub

If you want to keep the other worksheet formulas, use...
Option Explicit
'
Sub DeleteLinksOnly()
Dim Cell As Range, FirstAddress As String, Temp As String
'
Application.ScreenUpdating = False
With ActiveSheet.UsedRange
Set Cell = .Find("=*!", LookIn:=xlFormulas, LookAt:=xlPart)
On Error GoTo Finish
FirstAddress = Cell.Address
Do
Temp = Cell
Cell.ClearContents
Cell = Temp
Set Cell = .FindNext(Cell)
Loop Until Cell Is Nothing Or Cell.Address = FirstAddress
.Hyperlinks.Delete
End With
Application.ScreenUpdating = True
End Sub