Delete external named range

[theta]

Hi

I have a workbook that contains named ranges linked to another sheet

I need a macro to delete all external named ranges

I copy a sheet ("Client data") that contains vlookups etc that contain named table ranges

A macro then pastes these external formulas as values

So now need to delete named ranges

My ***IDEAL*** solution would be to modify the below code so that if a cell contains an external ref (currently does this) or has a named range that has an external ref (would like it to do this) then it will paste as values

Maybe then call another function to delete all external ref named ranges? (keep the functions seperate so I can use one and not the other when the choice suits)

Function DeleteLinks(ws As Worksheet)
'Convert external formula links to values
Dim r As Range, sFormula As String, i As Integer
    If ws Is Nothing Then Exit Function
    ws.Activate
    For Each r In ws.UsedRange
'        If Len(sFormula) > 0 Then
        If r.HasFormula = True Then
            sFormula = r.Formula
            If Left$(sFormula, 1) = "=" Then
                If InStr(sFormula, "[") > 1 Then
                            On Error Resume Next
                            ' in case the worksheet is protected
                            r.Formula = r.Value
                            On Error GoTo 0
                End If
            End If
        End If
    Next r
    Set r = Nothing
    End If
End Function

 

[GlennUK]

Won't simply breaking the links do everything you need?

 

[theta]

What would be the best way to do this using VBA?

Maybe as a function so that I may call it several times...

 

[GlennUK]

Edit the links, and break links while using the macro recorder. Is the code generated any use? This would be a subroutine or macro, and not a "Function".

 

[theta]

Hi Glenn

Break links works fine...but external named ranges just become #REF

Is there any VBA to delete external named ranges?

(or named ranges that are #REF, although I think both instances are different in principle)

 

[theta]

Found this post...think it will do the trick

Not sure which of the 4 solutions is best?

The unqualified (default to active)

http://excel.bigresource.com/Track/excel-tb5XAKnO/

 

[theta]

Ignore...wrong post