VBA Express : Excel - Delete spreadsheet links to other sheets or workbooks (Leave values and code)

Excel

Delete spreadsheet links to other sheets or workbooks (Leave values and code)

Ease of Use

Easy

Version tested with

2000

Submitted by:

johnske

Description:

After running the code all links are deleted from the selected region but values and spreadsheet code are retained...

Discussion:

When files are "archived" linked cells are often not needed to be linked, but the 'values' in the cells may need to be retained. Alternatively, you may want to remove SOME links while retaining others. Also, in some versions of Excel, you may not be able to easily access the "Remove Links" function provided in the later versions. (Or you may simply be unable to find it)

Code:

instructions for use

			Option Explicit 
 
Sub DeleteLinks_Selection() 
    Dim Cell As Range, FirstAddress As String, Temp As String 
     'delete all links from selected cells
    Application.ScreenUpdating = False 
    With Selection 
        Set Cell = .Find("=*!", LookIn:=xlFormulas, searchorder:=xlByRows, _ 
        LookAt:=xlPart, MatchCase:=True) 
        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 
    End With 
Finish: 
End Sub

How to use:

Open an Excel workbook
Select Tools/Macro/Visual Basic Editor
In the VBE window, select Insert/Module
Copy and paste the code into the Module
Now select File/Close and Return To Microsoft Excel
Save the workbook
Select the cells where you want links any deleted
Select Tools/Macro/Macros.../DeleteLinks_Selection/Run

Test the code:

An example to experiment with is included in the zip attachment

Sample File:

DeletingLinks.zip 7.95KB

Approved by mdmackillop

This entry has been viewed 153 times.